Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

max sales of last 6 months

Hello,

Can you help me with my issue? I have a database like this:

skudateqntysales
a01.01.20121030
b02.01.20122060
c02.01.2012515
a05.01.20123090
a10.01.20121030
b03.02.20123060
c03.02.201250150
a25.02.20122060
b07.03.20121030
b10.03.201245135
a10.03.20121560
c15.03.20121030

I want to get straight table, which will show me month in which was maximum sales during last 6 months and I want to see sum of this month too. This table must include all maximums for all sku. For example: if I choose  march 2012, it will show max sales since october 2011 and if I choose december 2011, it will show since july 2011.

I try to use something like this

max(aggr(sum({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} [sales]),year,month,[sku]))

but it worked only if i didn't choose any month.

Do you have any ideas?

Thanks for your help, Bill.

some information:

month, year - from another table like this:

Datemonthquarteryear
01.01.2012januaryQ12012
02.01.2012januaryQ22012
03.01.2012januaryQ32012
04.01.2012januaryQ42012

Month Year from script:

MonthName(DATE(Date, 'DD/MM/YYYY')) AS [Month Year]

1 Solution

Accepted Solutions
fosuzuki
Valued Contributor II

max sales of last 6 months

Try inserting the same set analysis expression in the outer max() function:

max({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} aggr(sum({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} [sales]),year,month,[sku]))

The concept is that the max() function also has to have the set analysis expression to override the current $ when you select any month.

Hope this helps you.

Regards,

Fernando

2 Replies
fosuzuki
Valued Contributor II

max sales of last 6 months

Try inserting the same set analysis expression in the outer max() function:

max({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} aggr(sum({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} [sales]),year,month,[sku]))

The concept is that the max() function also has to have the set analysis expression to override the current $ when you select any month.

Hope this helps you.

Regards,

Fernando

Not applicable

max sales of last 6 months

Thank you. It works perfect.