Rolling 4 and 12 month data in Qlikview

[#1]

Hi,

I am a new learner in qlikview. I am looking for inputs on rolling 4 month and 12 month data.

For eg. If my month is March then rolling 4 months will be Feb, Jan, Dec and Nov

I have used the below expressions but it is not showing any data:

= Sum({<date= {">=$(=Date(AddMonths(max(date),-3),'DD-MMM-YYYY')) <=$(=Date(max(date),'DD-MMM-YYYY'))"}>}Units)

= Sum({<date= {">=$(=Date(AddMonths(max(date),-11),'DD-MMM-YYYY')) <=$(=Date(max(date),'DD-MMM-YYYY'))"}>}Packs)

Could you please provide your inputs.

Thanks in advance!!!!

Rohit

[muthukumar77]

Hi,

What is Date format?

[PrashantSangle]

Hi,

If your month max(date) month is march and you want to exclude it then

try below

= Sum({<date= {">=$(=Date(Monthstart(AddMonths(max(date),-4)),'DD-MMM-YYYY')) <=$(=Date(MonthEnd(AddMonths(max(date),-1)),'DD-MMM-YYYY'))"}>}Units)

Regards

[#4]

Hi MUthukumar,

It's DD-MMM-YYYY i.e. 14-Mar-2017

Thanks,

[Anil_Babu_Samineni]

Seems, your expression is okay to me. So, I assume your selection will happen for date and you are expecting from Month'

May be this? Or please provide sample

= Sum({<date= {">=$(=AddMonths(max(Month),-3)) <=$(=max(Month))"}>}Units)

= Sum({<date= {">=$(=AddMonths(max(Month),-11)) <=$(=max(Month))"}>}Packs)

[muthukumar77 · Accepted solution]

Hi,

Recheck your date format.

I think format is problem.

Try like,

=Sum({$<Date={">=$(=Date(AddMonths(max(Date),-3),'MM/DD/YYYY'))<=$(=Date(max(Date),'MM/DD/YYYY'))"}>}Unit)

=Sum({$<Date={">=$(=Date(AddMonths(max(Date),-11),'MM/DD/YYYY'))<=$(=Date(max(Date),'MM/DD/YYYY'))"}>}Packs)

[#7]

I will try it and let you know with updates.

Thanks,

Rohit

[muthukumar77]

Did this work?

[#9]

I am still getting the same issue, please advice.

Thanks,

[Anil_Babu_Samineni]

Can you provide sample to work on it