Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
Chloe19
Contributor II
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)
8 Replies
Vikash
Contributor III
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

Chloe19
Contributor II
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)

 

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

Chloe19
Contributor II
Contributor II
Author

Hi VK

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

Chloe

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

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

 

Radford85
Contributor
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

Chloe19
Contributor II
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.