# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for
Did you mean:  Contributor II

## Cumulative Month

Hi

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

Labels (2)

• ### Scripting

8 Replies  Contributor III

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,

VK  Contributor II
Author

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

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,

VK  Contributor II
Author

Hi VK

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

Chloe  Contributor III

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?

VK  Contributor II
Author

Hi

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  Contributor

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  Contributor II
Author

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. Community Browser