topic Re: Cumulative Month in QlikView

Cumulative Month

Chloe19 — Fri, 23 Jul 2021 04:24:48 GMT

Can someone help me with the formula for cumulative but only for:

Cumulative for THIS MONTH

then separate formula for:

Cumulative for LAST MONTH

then separate formula for:

Cumulative for 2 MONTHS AGO

I have a variable already summed up $(vSUM_OF_X) and a date field [Effective Date].

Thanks

Re: Cumulative Month

Vikash — Fri, 23 Jul 2021 05:32:44 GMT

Hi Cloe,

You can achieve this by using set expression. Try the below expressions-

For This Month-
sum({$<Date={">=$(=num(monthstart(max(Date)))) <=$(=num(monthend(max(Date))))"}>}Sales)

For Last Month -

sum({$<Date={">=$(=num(monthstart(max(Date),-1))) <=$(=num(monthend(max(Date),-1)))"}>}Sales)

For 2nd Last Month -

sum({$<Date={">=$(=num(monthstart(max(Date),-2))) <=$(=num(monthend(max(Date),-2)))"}>}Sales)

Note:- Use your date dimensions and Measures. like- For Date, use [Effective Date]..etc.

Regards,

Re: Cumulative Month

Chloe19 — Fri, 23 Jul 2021 10:54:56 GMT

Hi VK

Thanks for the response.

Tried it, while the expression says its ok there is no problem, I do get a result of 0 for the current month and for the 2 months ago just returns the total sum all months, not cumulative for the month which is 2 months ago.

sum({$<Date={">=$(=num(monthstart(max([Effective Date]),-2))) <=$(=num(monthend(max([Effective Date]),-2)))"}>}TOTAL_CASH)

Re: Cumulative Month

Vikash — Sun, 25 Jul 2021 10:20:34 GMT

Hi Cloe,

You need to check the date value from your date field. Check below -

sum({$<[Effective Date]={">=$(=num(monthstart(max([Effective Date]),-2))) <=$(=num(monthend(max([Effective Date]),-2)))"}>}TOTAL_CASH)

Try the above expression, I think it should work now.

Regards,

Re: Cumulative Month

Chloe19 — Mon, 26 Jul 2021 04:53:32 GMT

Hi VK

Ah I see I missed the date replacement there, however still the same issue, still get 0

Chloe

Re: Cumulative Month

Vikash — Mon, 26 Jul 2021 05:12:47 GMT

Hi Cloe,

The same expression works for me. There must be some Date type of issue.

Could you please let me know the format of your [Effective Date] field?

Re: Cumulative Month

Chloe19 — Mon, 26 Jul 2021 05:25:26 GMT

The filed is DD/MM/YYYY

What I am trying to do is this formula from POWER BI:

Cummulative Cash = CALCULATE([Total Cash],FILTER(ALLSELECTED('Date'),'Date'[Date] <= MAX('Date'[Date])))

So the result would look like the attached screenshot out of POWER BI

Chloe

Re: Cumulative Month

Radford85 — Tue, 27 Jul 2021 03:55:07 GMT

Means it takes the previous months values too. Ex; if I select Feb month means it shows the cumulative sum of Jan+Feb. Similarly for Mar = JanFebMar. Here in this calculation right now I using Order Date but I want to calculate the same formula based on Fiscal month .

Kroger Feed

Re: Cumulative Month

Chloe19 — Mon, 26 Jul 2021 07:29:42 GMT

So the below works for cumulative

=RangeSum(Above(TOTAL Sum(TOTAL_CASH), 0, RowNo(TOTAL)))

However am trying to write a cumulative for previous month and month before that.

So then no matter what month I filter on it will take into account the previous month and the month before that.

Example in the attached, even though I filtered date to be FEB only, I still have the cumulative for the previous 2 months.