topic Re: Line chart showing values for previous 12 months in QlikView

Line chart showing values for previous 12 months

Mon, 14 Dec 2015 12:15:11 GMT

Hi There, On the left side in my dashboard i have my Actual Trends and right hand side shows FY and Period. I want the actual trend Line chart to show the values for previous 12 months, say in click on FY 2016, Period: 3,, i want actual trends to show values from 2015 03 to 2016 03..... Can anybody help me with the same please ? Thanks indeed.

Re: Line chart showing values for previous 12 months

Anonymous — Mon, 14 Dec 2015 12:28:39 GMT

see this, really helpful for you:

Set Analysis for certain Point in Time

Re: Line chart showing values for previous 12 months

Peter_Cammaert — Mon, 14 Dec 2015 12:46:14 GMT

Use set analysis to adjust the dimension values (Timeline) you want to display.

Imagine that you have a Date field somewhere (MyDate) that also exists in Month and Year versions. The latter can be shown as listboxes. The former will be used to control set analysis like this:

=sum({<MyDate = {">=$(=MonthStart(AddMonths(Max(MyDate), -12)))<=$(=MonthEnd(Max(MyDate)))"}>} SalesAmount)

Peter

Re: Line chart showing values for previous 12 months

Mon, 14 Dec 2015 13:48:04 GMT

Thanks a million

Re: Line chart showing values for previous 12 months

Mon, 14 Dec 2015 13:48:25 GMT

Thank you very much

Re: Line chart showing values for previous 12 months

Mon, 14 Dec 2015 13:55:09 GMT

Hey Balaraj,,

This is my expression in here :

if(Headline_Fig= 'Gross Margin', sum({<FY={$(vMaxYear_CompletePeriod)},Period={'>=$(=Min(Period))<=$(=Max(Period))'}, Flag={'Actual'},[Headline_Fig]={'Gross Margin'}>}Amt),

if(Headline_Fig= 'Controllable Contribution', sum({<FY={$(vMaxYear_CompletePeriod)},Flag={'Actual'},[Headline_Fig]={'Controllable Contribution'}>}Amt),

if(Headline_Fig= 'Gross Revenues', sum({<FY={$(vMaxYear_CompletePeriod)},Flag={'Actual'},[Headline_Fig]={'Gross Revenues'}>}Amt),

if(Headline_Fig= 'Net Revenue', sum({<FY={$(vMaxYear_CompletePeriod)},Flag={'Actual'},[Headline_Fig]={'Net Revenue'}>}Amt)))))

What do i need to change in here to make show values for previous 12 months ?

Thanks for the help.. Much appreciated.... Thanks indeed !!

Re: Line chart showing values for previous 12 months

Mon, 14 Dec 2015 14:28:23 GMT

Hey Peter,

This is my expression in here :

if(Headline_Fig= 'Gross Margin', sum({<FY={$(vMaxYear_CompletePeriod)},Period={'>=$(=Min(Period))<=$(=Max(Period))'}, Flag={'Actual'},[Headline_Fig]={'Gross Margin'}>}Amt),

if(Headline_Fig= 'Controllable Contribution', sum({<FY={$(vMaxYear_CompletePeriod)},Flag={'Actual'},[Headline_Fig]={'Controllable Contribution'}>}Amt),

if(Headline_Fig= 'Gross Revenues', sum({<FY={$(vMaxYear_CompletePeriod)},Flag={'Actual'},[Headline_Fig]={'Gross Revenues'}>}Amt),

if(Headline_Fig= 'Net Revenue', sum({<FY={$(vMaxYear_CompletePeriod)},Flag={'Actual'},[Headline_Fig]={'Net Revenue'}>}Amt)))))

What do i need to change in here to make show values for previous 12 months ?

Thanks for the help.. Much appreciated.... Thanks indeed !!

Re: Line chart showing values for previous 12 months

Peter_Cammaert — Mon, 14 Dec 2015 16:04:15 GMT

Probably you cannot use your variable vMaxYear_CompletePeriod and Min/Max(Period) anymore because calculating the lower limit date that is 12 months earlier is ugly (wasted energy) with these fields. Do your data records have a real date field attached? A calendar object?

Peter

Re: Line chart showing values for previous 12 months

Mon, 14 Dec 2015 16:23:40 GMT

Hey Peter,,

Yes it has real date field.. and calendar object

Re: Line chart showing values for previous 12 months

Peter_Cammaert — Mon, 14 Dec 2015 16:50:45 GMT

In your nested IF, there is only one sum() that takes into account your Period selection. The others don't. Do you want them to include the entire previous year?

Re: Line chart showing values for previous 12 months

Tue, 15 Dec 2015 09:35:32 GMT

Hey Peter, This is my Calendar tab that shows :

//Temporarily coming from excel as PE Clone doesn't have latest periods

tblControlPeriods:

LOAD PeriodIndex,

YearEndDate,

PeriodStartDate,

PeriodEndDate,

Period,

FY,

Quarter,

PeriodStatus,

PeriodDescription,

PeriodWIPMax,

PeriodDRSMax,

PeriodWIPClearMax,

PeriodDRSClearMax,

PeriodVATMax,

[Date(CashStartDate)],

[Date(CashEndDate)],

FYPeriod,

CalMonth,

CalYear

FROM

[Excel Sources\Calendar_test.xls]

(biff, embedded labels, table is Sheet1$);