2 Replies Latest reply: Dec 14, 2017 12:52 PM by Gonzalo Yagüe Suñol RSS

    Cumulative Sum when a condition is met (Qlik sense)

    Gonzalo Yagüe Suñol

      Dear all,

      As may be seen below, we have data of revenue in different cities of Spain and different weeks, coming from different industries. (It's not the real data set, it's just an example)

      City            Week        Industry        Revenue

      Barcelona  1                Finance        375

      Barcelona  1                Logistics      407 

      Barcelona  2                Finance        375

      Barcelona  2                Logistics      407

      Barcelona  3                Finance        475

      Barcelona  3                Logistics      507

      Madrid        1                Finance        280

      Madrid        1                Logistics      310

      Madrid        2                Finance        290

      Madrid        2                Logistics      295

      Madrid        3                Finance        290

      Madrid        3                Logistics      295

      I have a filter with the Week values.  I need to create the following combo chart in qlik sense (all the measures will be displayed as bar charts). As dimension I will have City. One measure should be the cumulative sum (from the first to the maximum week selected on the filter) of revenue in Finance industry and the other measure in Logistics.

      On the one hand, I am able to calculate the cumulative sum considering both industries together

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

      On the other hand, I am able to calculate the sum of revenue for each industry

      (  =Sum({$<Industry={"Logistics"}>}[Revenue]}).

      I think the solution should be similar to the following code but it does not work:

      (  =Sum({$<Week={"<=$(=Max(Week))"},Industry={"Logistics"}>}[Revenue]}).

      Just as notes:

      * Week field is an integer ranging from 1 to 3. It does not come from a date field.

      * I cannot use another filter for Industry field because I would like to display the sum of Revenue for each industry separately at any time.

      * I have tried to create a calculated field on the Data Manager (Revenue only for Logistics for example) but at the end I'm not obtaining the desired result. In addition, it might not be very efficient with much information. I would like to achieve it only with Set Analysis.

      I would really appreciate if anyone could help me solve this issue. Thank you very much in advance.

      Best regards,