Why the results are different although I'm using the same calculation in both charts

[Sam07]

I need help:

I am developing a report in Qlik Sense, which I have two charts, one card (KPI) and the other one a line chart. In both, I am using the same formula to calculate the metrics in both. The card is showing the values ​​of '26' and '72', however, it should show the same values ​​for November as the line chart, which are '36' and 75'. Does anyone know why this is happening?

When I select November in the filter, the card chart shows the correct value.

The formulas I am using for each metric are as follows:

avg({<[Fiscal Date]={">=$(=YearStart(max([Fiscal Date])))"},VFPBDAFLG_LOT_APPROVED={'1'},VFPBDAFLG_OUTLIER={'0'}>}VFPBDADAYS_TO_BE_RELEASED);

avg({<[Fiscal Date]={">=$(=YearStart(max([Fiscal Date])))"},VFPBDAFLG_LOT_APPROVED={'1'},VFPBDAFLG_OUTLIER={'1'}>}VFPBDADAYS_TO_BE_RELEASED);

[Lech_Miszkiewicz · Accepted solution]

Hi, 

very simple - your KPI shows avg across all the months for the period ([Fiscal Date]={">=$(=YearStart(max([Fiscal Date])))) which you can preview in expression editor. 

In reality it is an avg between M01 Apr and M08 Nov.

Also there is no surprise when you select single month that you get correct result as both charts show only one result then.

If your KPI should show only last month from the possible months in selection then you need to change your set analysis for the KPI object to reflect that.

cheers

[Sam07]

Hi Lech,

Thank you for your clarifying, I really appreciate it.

When I developed this metric I built it to do a cumulative sum over the months. For instance, the values for May (since it's our second month because I'm following the fiscal year) should be a result of a sum of avg(april) + avg(may). But according to your explanation my formula is not doing so.

I'm quite new on Qlik Sense, could you tell me what I need to change to reflect a cumulative sum over the months?

[Lech_Miszkiewicz]

Hi @Sam07 

• Currently it is not doing cumulative
  • For KPI it is doing avg of total
  • for trend it is doing avg for each month
• If I follow your definition that is a: SUM of AVG from each month then you need to do following:
  • Doing cumulative measure for KPI and chart will be different in this case
    • for KPI you would have to use
      • SUM(AGGR([Your Measure],[YourMonthField]))
    • For trend Chart you may consider using cumulative modifier on the measure or your expression will be somewhat like this:
      • RangeSum(Above(TOTAL [yourMeasure], 0, RowNo(TOTAL)))

cheers