Articles Comments

Free Excel Dashboards » Dashboard, Excel Dashboard, Excel Dashboard Software » Pivot Table

Pivot Table

Let say you have your data in the following format

Image

 

 

 

 

 

 

 

The column represents the year. Let us not concern about the other columns for now.

How can we represent the data above as below which shows the data grouped by the year where the current row year is added to only the next row year. Every row, looks ahead , get the next year value and sums it up and shows as a unique row.

 

Image

 

 

 

So the end result should be a pivot table that has 2008 and 2009 values summed up, 2009 and 2010 summed up.

Assuming there is no direct relationship between 2008 and 2009 we will try to derive algebric relation and see if we can get the desired result.

The solution assumes that there will be a starting year provided as a seed value. so in this case we will consider 2001 as the seed value.

On the dashboard, we can create a parameter so that we don’t have to hard code the value 2001

Take a look at the below screenshot

Image

 

 

 

 

 

 

 

The different columns are mod calculations to derive the final relation on how the alternative rows can be related.

Once we get a common value or the relation value, we can easily sum them up in the SQL

The first mod operation between Year and 2001 (fixed value or seed value) gives us sequential numbers

The second mod operation on top of the first mod gives us 010101 sequence.

So now looking at the pattern, if we subtract the second mod operation value from the first mod value, we get the column Diff

So now, 2001 and 2002 are related because their calculated diff value is 0, so are 2003 and 2004

But we also need rows that represent the sum between 2002 and 2003, 2004 and 2005 and so on. The above logic is missing those.

In our calculation if we add the two mods, we get below

Image

 

 

 

 

 

 

 

Now, we have years (2002,2003), (2004,2005), (2006,2007) related.

So in our query, if we join the two SQL using union then we can expect to see all the row combinations

Now let us implement it on the dashboard.

The sample excel file is as shown below

Image

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

We will connect to this excel file and pull this table on the dashboard (Check this tutorial how to connect excel file)

here is the screenshot of the excel file on the dashboard

 

Image

 

 

 

 

 

 

 

 

 

Next we split the date column into year and month columns

Image

 

 

 

 

 

 

 

 

 

Image

 

 

 

 

 

 

 

 

We select the year and month columns (need to scroll down when selecting month)

So we get the table (qlet) as below

Image

 

 

 

 

 

 

 

 

 

 

NOTE: We can manually change the month format to remove the first two digits. for e.g 01-Jan to just “Jan”. We edit the query and update the following line format(”some_date”,’mmm’) as “so_Mth_Disp”, (we just added an extra m and remove the other format)

We also add the following columns mod1, mod2, related field, r1 and r2 which are explained below

<code>

select
“some_date”,
year(”some_date”) as “so_Year”,
“so_Year” – 2001 as “mod1″ ,
mod1 – fix(mod1/2)*2 as mod2,
mod1 – mod2 as “related_field”,
related_field + 2001 as r1,
related_field + 2001 + 1 as r2,

format(”some_date”,’mmm’) as “so_Mth_Disp”,
format(”some_date”,’yyyy-mm(mmm)’) as “so_Yr_Mth_NN”,
format(”some_date”,’q’) as “so_Qtr”,
format(”some_date”,’yyyy-ww’) as “so_Yr_wk”,
datepart(’y’,”some_date”) as “so_Yr_Dy” ,
“some_amount”
From (Select
she.”some_date” ,
she.”some_amount”
From
[Sheet1$] she
) as dateColsx

</code>

Since Excel odbc does not support the mod function, we use division and multiplication to arrive at the mod value

We have hard coded the value to 2001 but we can always replace this with the parameter from the dashboard which we will do it at the very end.

Having all the related columns derived based on our excel model earlier now it is time to work on the month columns. Since all the dates are rows, we need to somehow show them as columns.

First step is we use the switch statement to separate each month into a column.

here is the SQL with the switch statement

<code>

select “so_Year”, r1&’ — ‘&r2 as yr
,switch(so_mth_disp = ‘Jan’, some_amount, so_mth_disp <> ‘Jan’,0) as “m1″
,switch(so_mth_disp = ‘Feb’, some_amount, so_mth_disp <> ‘Feb’,0) as “m2″
,switch(so_mth_disp = ‘Mar’, some_amount, so_mth_disp <> ‘Mar’,0) as “m3″
,switch(so_mth_disp = ‘Apr’, some_amount, so_mth_disp <> ‘Apr’,0) as “m4″
,switch(so_mth_disp = ‘May’, some_amount, so_mth_disp <> ‘May’,0) as “m5″
,switch(so_mth_disp = ‘Jun’, some_amount, so_mth_disp <> ‘Jun’,0) as “m6″
,switch(so_mth_disp = ‘Jul’, some_amount, so_mth_disp <> ‘Jul’,0) as “m7″
,switch(so_mth_disp = ‘Aug’, some_amount, so_mth_disp <> ‘Aug’,0) as “m8″
,switch(so_mth_disp = ‘Sep’, some_amount, so_mth_disp <> ‘Sep’,0) as “m9″
,switch(so_mth_disp = ‘Oct’, some_amount, so_mth_disp <> ‘Oct’,0) as “m10″
,switch(so_mth_disp = ‘Nov’, some_amount, so_mth_disp <> ‘Nov’,0) as “m11″
,switch(so_mth_disp = ‘Dec’, some_amount, so_mth_disp <> ‘Dec’,0) as “m12″

from
(
select
“some_date”,
year(”some_date”) as “so_Year”,
“so_Year” – 2001 as “mod1″ ,
mod1 – fix(mod1/2)*2 as mod2,
mod1 – mod2 as “related_field”,
related_field + 2001 as r1,
related_field + 2001 + 1 as r2,

format(”some_date”,’mmm’) as “so_Mth_Disp”,
format(”some_date”,’yyyy-mm(mmm)’) as “so_Yr_Mth_NN”,
format(”some_date”,’q’) as “so_Qtr”,
format(”some_date”,’yyyy-ww’) as “so_Yr_wk”,
datepart(’y’,”some_date”) as “so_Yr_Dy” ,
“some_amount”
From (Select
she.”some_date” ,
she.”some_amount”
From
[Sheet1$] she
) as dateColsx
)

</code>

here is the resulting table

Image

 

 

 

 

 

 

 

 

 

 

 

 

 

If you notice we concatenated r1 and r2 to create a single new column and we named it as ‘Yr’

Finally we sum all the month columns and group by the new ‘Yr’ column

select yr,
sum(m1) as jan,
sum(m2) as feb,
sum(m3) as Mar,
sum(m4) as apr,
sum(m5) as may,
sum(m6) as jun,
sum(m7) as jul,
sum(m8) as aug,
sum(m9) as sep,
sum(m10) as oct,
sum(m11) as nov,
sum(m12) as dec

from
(
select “so_Year”, r1&’ — ‘&r2 as yr
,switch(so_mth_disp = ‘Jan’, some_amount, so_mth_disp <> ‘Jan’,0) as “m1″
,switch(so_mth_disp = ‘Feb’, some_amount, so_mth_disp <> ‘Feb’,0) as “m2″
,switch(so_mth_disp = ‘Mar’, some_amount, so_mth_disp <> ‘Mar’,0) as “m3″
,switch(so_mth_disp = ‘Apr’, some_amount, so_mth_disp <> ‘Apr’,0) as “m4″
,switch(so_mth_disp = ‘May’, some_amount, so_mth_disp <> ‘May’,0) as “m5″
,switch(so_mth_disp = ‘Jun’, some_amount, so_mth_disp <> ‘Jun’,0) as “m6″
,switch(so_mth_disp = ‘Jul’, some_amount, so_mth_disp <> ‘Jul’,0) as “m7″
,switch(so_mth_disp = ‘Aug’, some_amount, so_mth_disp <> ‘Aug’,0) as “m8″
,switch(so_mth_disp = ‘Sep’, some_amount, so_mth_disp <> ‘Sep’,0) as “m9″
,switch(so_mth_disp = ‘Oct’, some_amount, so_mth_disp <> ‘Oct’,0) as “m10″
,switch(so_mth_disp = ‘Nov’, some_amount, so_mth_disp <> ‘Nov’,0) as “m11″
,switch(so_mth_disp = ‘Dec’, some_amount, so_mth_disp <> ‘Dec’,0) as “m12″

from
(
select
“some_date”,
year(”some_date”) as “so_Year”,
“so_Year” – 2001 as “mod1″ ,
mod1 – fix(mod1/2)*2 as mod2,
mod1 – mod2 as “related_field”,
related_field + 2001 as r1,
related_field + 2001 + 1 as r2,

format(”some_date”,’mmm’) as “so_Mth_Disp”,
format(”some_date”,’yyyy-mm(mmm)’) as “so_Yr_Mth_NN”,
format(”some_date”,’q’) as “so_Qtr”,
format(”some_date”,’yyyy-ww’) as “so_Yr_wk”,
datepart(’y’,”some_date”) as “so_Yr_Dy” ,
“some_amount”
From (Select
she.”some_date” ,
she.”some_amount”
From
[Sheet1$] she
) as dateColsx
)
)
group by yr

Result

Image

 

 

 

 

 

This almost looks like what we needed but it is missing the rows 2002 — 2003, 2004 — 2005 etc

We simply edit the SQL, duplicate it and append to the same SQL as a union query. The only difference would for the bottom union is that the related_field would be the addition of mod1 and mod2

select yr,
sum(m1) as jan,
sum(m2) as feb,
sum(m3) as Mar,
sum(m4) as apr,
sum(m5) as may,
sum(m6) as jun,
sum(m7) as jul,
sum(m8) as aug,
sum(m9) as sep,
sum(m10) as oct,
sum(m11) as nov,
sum(m12) as dec
from
(
select “so_Year”, r1&’ — ‘&r2 as yr
,switch(so_mth_disp = ‘Jan’, some_amount, so_mth_disp <> ‘Jan’,0) as “m1″
,switch(so_mth_disp = ‘Feb’, some_amount, so_mth_disp <> ‘Feb’,0) as “m2″
,switch(so_mth_disp = ‘Mar’, some_amount, so_mth_disp <> ‘Mar’,0) as “m3″
,switch(so_mth_disp = ‘Apr’, some_amount, so_mth_disp <> ‘Apr’,0) as “m4″
,switch(so_mth_disp = ‘May’, some_amount, so_mth_disp <> ‘May’,0) as “m5″
,switch(so_mth_disp = ‘Jun’, some_amount, so_mth_disp <> ‘Jun’,0) as “m6″
,switch(so_mth_disp = ‘Jul’, some_amount, so_mth_disp <> ‘Jul’,0) as “m7″
,switch(so_mth_disp = ‘Aug’, some_amount, so_mth_disp <> ‘Aug’,0) as “m8″
,switch(so_mth_disp = ‘Sep’, some_amount, so_mth_disp <> ‘Sep’,0) as “m9″
,switch(so_mth_disp = ‘Oct’, some_amount, so_mth_disp <> ‘Oct’,0) as “m10″
,switch(so_mth_disp = ‘Nov’, some_amount, so_mth_disp <> ‘Nov’,0) as “m11″
,switch(so_mth_disp = ‘Dec’, some_amount, so_mth_disp <> ‘Dec’,0) as “m12″

from
(
(
select
“some_date”,
year(”some_date”) as “so_Year”,
“so_Year” – 2001 as “mod1″ ,
mod1 – fix(mod1/2)*2 as mod2,
mod1 – mod2 as “related_field”,
related_field + 2001 as r1,
related_field + 2001 + 1 as r2,

format(”some_date”,’mmm’) as “so_Mth_Disp”,
format(”some_date”,’yyyy-mm(mmm)’) as “so_Yr_Mth_NN”,
format(”some_date”,’q’) as “so_Qtr”,
format(”some_date”,’yyyy-ww’) as “so_Yr_wk”,
datepart(’y’,”some_date”) as “so_Yr_Dy” ,
“some_amount”
From (
Select
she.”some_date” ,
she.”some_amount”
From
[Sheet1$] she
)
as dateColsx
union
select
“some_date”,
year(”some_date”) as “so_Year”,
“so_Year” – 2001 as “mod1″ ,
mod1 – fix(mod1/2)*2 as mod2,
mod1 + mod2 -1 as “related_field”,
related_field + 2001 as r1,
related_field + 2001 + 1 as r2,

format(”some_date”,’mmm’) as “so_Mth_Disp”,
format(”some_date”,’yyyy-mm(mmm)’) as “so_Yr_Mth_NN”,
format(”some_date”,’q’) as “so_Qtr”,
format(”some_date”,’yyyy-ww’) as “so_Yr_wk”,
datepart(’y’,”some_date”) as “so_Yr_Dy” ,
“some_amount”
From (
Select
she.”some_date” ,
she.”some_amount”
From
[Sheet1$] she
)
as dateColsx

)
)

)
group by yr

Image

For more information visit Pivot Table

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

Filed under: Dashboard, Excel Dashboard, Excel Dashboard Software

Comments are closed.