Help with Cumulative KPI in Qlik Sense SaaS Using MonthYear Filter

[Qlik_Wizard]

Hi everyone,

I'm working on building a KPI in Qlik Sense SaaS, and my data source is an Excel file. I have a field called MonthYear, but when loading it, the values show up as numbers like 45627, 45717, etc.

So, in my data load script, I'm doing the following:

• MonthStart(MonthYear) as MonthYearNum,
• Date(MonthStart(MonthYear), 'MMM YYYY') as MonthYear

I'm using the formatted MonthYear field (MMM YYYY) in a filter pane. For the filter display, I’m using:

• Month(MonthYear)

Now, I want to create a KPI that gives me the cumulative sum of values up to the selected month. For example:

• Jan = 100

• Feb = 200

• Mar = 300

If I select March, the KPI should show 600 (100+200+300).

I'm using the following expression:

Sum({<Metric = {'Non-compliant'}, MonthYearNum = {"<=$(=Max(MonthYearNum))"}>} Value)

However, this only gives me the value for the selected month, not the cumulative sum up to the selected month.

Could someone please help me understand what I’m doing wrong or suggest a better approach?

Thanks in advance!

[Vegar]

You will probably need to ignore any selection in the field MonthYea in order for this to work. Try this

Sum({<Metric = {'Non-compliant'}, MonthYearNum = {"<=$(=Max(MonthYearNum))"}, MonthYear>} Value)

[Qlik_Wizard]

Hey! Thank you soo much for responding!! I tried this but it's not working. 

FYI I corrected my MonthYear data format from the excel file and my load script looks like this