Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Cumulative sum based on two dimensions (Qlik Sense)

Hi all,

On the one hand, I have bar chart representing the sum of Sales (measure) per country (dimension). On the other hand, I have a filter with the number of weeks (from 1 to 52, all of them as integers...).

This is a long exercise. The first step would be to display the sum of sales, considering only the data related from number one to the maximum week selected. In other words, if the maximum week selected was 5, it should display the sum of sales for each country from first to fifth week.

I know there are some threads about this topic but I have not been able to achieve it. Here we can see some of the codes I have used:

* Sum({$ <Week = {<=$(=Max(Week))}>}[Sales])

* Sum({$ <Week = {'<=$(=Max(Week))'}>}[Sales])

* Sum({$ <Week = {"<=$(=Max(Week))"}>}[Sales])


I have read on several threads about the importance of single and double quotes. Unfortunately, if I use them, Max and Week are not colored as function and field respectively and obviously it does not work. If I don't use them, Max and Week are colored well but it does not work.


I would really thank you if anyone can help me. It should be very easy but I am stuck.


Best regards,


Gonzalo

3 Replies
sunny_talwar

Although you don't see colors, it doesn't really mean that your expression is incorrect.... not sure why this didn't work for you... have you tried clicking okay to check if you get any result or not?

Sum({$ <Week = {"<=$(=Max(Week))"}>}[Sales])

Anonymous
Not applicable
Author

Hi Sunny,

Thank you very much for your quick reply.

I always click okay but have never seen the result.

I forgot to mention that originally I had [Original_Week] field as a string ('2017-W01', '2017-W02', '2017-W03'....). In data manager, I clicked on edit the original table and created a new field, called Week, as Num(Right(Original_Week,2). If I select any Week in the filter, the original bar chart (Sum(Sales) vs Country) is filtered properly. For that reason, I don't think it should be the problem.

Best regards,

Gonzalo

sunny_talwar

I agree, I don't see this as a problem as well.. would you be able to post screenshots of the expression and the script?