topic Re: Last 3 month usage in QlikView

Last 3 month usage

Tue, 28 Jan 2014 09:42:00 GMT

I am trying to to calculate the last 3 months quantity. I have set this up as set analysis as I don't want dates selected from the list box affecting the results.

The expression as it stands is showing the last 2 months and the current month to date. I.e. today it is showing usage from 1^st November to 28^th January.

Sum ({$<YearMonth={">=$(=Date(AddMonths(Max(YearMonth),-2),'YYYY-MMM'))"} >} Quantity/3)

As I am trying to calculate average monthly usage (based on the last 3 months) I would need the expression to calculate for exactly 3 months. This could be the last 3 complete months (i.e. Oct, Nov & Dec) or 3 months from today’s date.

I appreciate any help that you could provide.

Re: Last 3 month usage

Tue, 28 Jan 2014 09:49:18 GMT

Hi James

so you have it

Sum ({$<YearMonth={">=$(=Date(AddMonths(Max(YearMonth),-3),'YYYY-MMM')) <=$(=Date((Max(YearMonth),'YYYY-MMM'))"} "} >} Quantity)

gives you 3 months from max(YearMonth) selected

best regards

Chris

Re: Last 3 month usage

er_mohit — Tue, 28 Jan 2014 10:03:32 GMT

Try this You Date Field

Sum ({<DateField={'>=$(=num(monthstart(max(Datefield),-3)))<$(=num(Monthend(max(Datefield),-1)))'},YearField=>} Quantity)/3

Re: Last 3 month usage

jagan — Tue, 28 Jan 2014 10:09:57 GMT

Hi,

If you have a date field in your data model then try like this

=Sum ({<DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName),-3))<$(=MonthEnd(Max(DateDimensionName),-1))'},YearDimensionName=, MonthDimensionName=>} Quantity)/3

Regards,

Jagan.