topic Re: Last 12 months expression help! in QlikView

Last 12 months expression help!

amit_saini — Mon, 07 Dec 2015 10:13:29 GMT

Hi Folks,

We are having one of our application in which

Dimension: = Month ([GSE Overall.GSEoverallmonth]) & '-' & Year ([GSE Overall.GSEoverallmonth])

Expression: [GSE Overall.ppm]

Below is the result table , now here we want one column to show last 12 months ppm rate.

Please suggest how to do it.

Actually we are not using any calendar for this Dashboard, otherwise I could have done something similar:

Sum({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), -1), 'MMM-YY'))"} >} Sales)

Thanks,

Re: Last 12 months expression help!

Kushal_Chawda — Mon, 07 Dec 2015 10:33:34 GMT

what is the values in GSE Overall.GSEoverallmonth Field

Re: Last 12 months expression help!

thakkarrahul01 — Mon, 07 Dec 2015 10:35:42 GMT

Hi amit,

As you mentioned you have dimension information in month-year format. Don't you have field containing date entries against each row in the table, if its the case then expression you wrote should be working fine.

Now question is , in table you have included monthly column using calculations then How you managed to calculate them using expression?

Re: Last 12 months expression help!

amit_saini — Mon, 07 Dec 2015 10:38:59 GMT

Thanks,

Re: Last 12 months expression help!

nagarjuna_kotha — Mon, 07 Dec 2015 10:59:34 GMT

Hi Amit,

Try this Expression :

sum({<Date = {">=$(=MonthStart(Max(Date), -12))<=$(=MonthEnd(Max(Date)))"},Year=,Month=>}Sales).

-Nagarjun

Re: Last 12 months expression help!

Kushal_Chawda — Mon, 07 Dec 2015 12:07:51 GMT

I am not sure but you can try this

=rangesum(after( sum( [GSE Overall.ppm]), 1,12 )) or rangesum(after( sum( [GSE Overall.ppm]), 0,12 ))

rangesum(after( only( [GSE Overall.ppm]), 1,12 )) or rangesum(after( only( [GSE Overall.ppm]), 0,12 ))

Re: Last 12 months expression help!

amit_saini — Tue, 08 Dec 2015 05:49:10 GMT

Any suggestions???

PFA of sample qvw

Thanks,

Re: Last 12 months expression help!

Kushal_Chawda — Tue, 08 Dec 2015 07:23:53 GMT

See the attached