Cumulative sum based on two dimensions (Qlik Sense)

[Anonymous]

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

[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]

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?