Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amit_saini
Honored Contributor III

Last 12 months expression help!

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,

AS

7 Replies

Re: Last 12 months expression help!

what is the values in GSE Overall.GSEoverallmonth Field

thakkarrahul01
Contributor

Re: Last 12 months expression help!

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?


amit_saini
Honored Contributor III

Re: Last 12 months expression help!

Thanks,

AS

nagarjuna_kotha
Valued Contributor II

Re: Last 12 months expression help!

Hi Amit,

Try this Expression :

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

-Nagarjun

Re: Last 12 months expression help!

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 ))

or

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

amit_saini
Honored Contributor III

Re: Last 12 months expression help!

Any suggestions???

PFA of sample qvw

Thanks,

AS

Re: Last 12 months expression help!

See the attached

Community Browser