Building Bar Charts using TTM (Trailing Twelve Months) data sets

Fri, 15 Oct 2010 03:46:27 GMT

Anyone figured out how to create a bar graph for data sets of 12 months per bar for a twelve month period?

Sample would be:

October data set - reports from sept 2009 to oct 2010

November data set - reports from oct 2009 to nov 2010 ect.

Building Bar Charts using TTM (Trailing Twelve Months) data sets

vgutkovsky — Fri, 15 Oct 2010 05:54:18 GMT

I've never seen this done, but I think you would need to create 12 expressions. Each expression would include a set analysis statement that would evaluate only the relevant 12 months. Example (October expression): sum({<date={">=9/1/2009<=10/1/2010"}>} Sales). Of course, you can set this dynamically with variables that would update based on user selections.

Regards,

Building Bar Charts using TTM (Trailing Twelve Months) data sets

johnw — Fri, 15 Oct 2010 19:20:59 GMT

What do you mean by 12 months per bar? Just the sum of the past 12 months, so a rolling 12 months? Or did you want the twelve months stacked, for instance? The most trivial approach would be to just set the expression to accumulation and accumulate 12 steps back, but I'm not sure if that's what you're after.

Re: Building Bar Charts using TTM (Trailing Twelve Months) data sets

Tue, 16 Jul 2013 13:45:08 GMT

This task is easy if you have a trailing built on static time periods (the last 12 month from today) then you can calculate time based on Today() variable. But when we talk about dinamic approach things gets complicated. Here is an example I worked on lately.

sum({<MonthPeriod={

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-1))&Num(Month(AddMonths(Today(),-1)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-1))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-1)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-1)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-2))&Num(Month(AddMonths(Today(),-2)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-2))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-2)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-2)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-3))&Num(Month(AddMonths(Today(),-3)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-3))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-3)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-3)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-4))&Num(Month(AddMonths(Today(),-4)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-4))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-4)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-4)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-5))&Num(Month(AddMonths(Today(),-5)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-5))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-5)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-5)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-6))&Num(Month(AddMonths(Today(),-6)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-6))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-6)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-6)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-7))&Num(Month(AddMonths(Today(),-7)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-7))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-7)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-7)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-8))&Num(Month(AddMonths(Today(),-8)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-8))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-8)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-8)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-9))&Num(Month(AddMonths(Today(),-9)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-9))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-9)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-9)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-10))&Num(Month(AddMonths(Today(),-10)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-10))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-10)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-10)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-11))&Num(Month(AddMonths(Today(),-11)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-11))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-11)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-11)),'00')))),

$(=if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)<>1, Year(AddMonths(GetFieldSelections(Year)& '-'&Num(Month(AddMonths(Today(),-1)),'00')&'-01',-12))&Num(Month(AddMonths(Today(),-12)),'00'),if(GetSelectedCount(Year)=1 and GetSelectedCount(Month)=1, Year(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-12))&Num(Month(AddMonths(GetFieldSelections(Year)& '-'& Num(MONTH_IN_YEAR,'00')&'-01',-12)),'00'), Year(AddMonths(Today(),-1))& Num(Month(AddMonths(Today(),-12)),'00'))))},Month=,Year=>}AMOUNT)

MonthPeriod is a format YYYYMM = '201307' which is in my time dimension. You need to build all the 12 functions in thae same object because if I create 12 variables the graph will not respond properly and become very unstable. I hope others have a better solution. Please let me know!

topic Re: Building Bar Charts using TTM (Trailing Twelve Months) data sets in QlikView

Building Bar Charts using TTM (Trailing Twelve Months) data sets

Building Bar Charts using TTM (Trailing Twelve Months) data sets

Building Bar Charts using TTM (Trailing Twelve Months) data sets

Re: Building Bar Charts using TTM (Trailing Twelve Months) data sets