topic Re: Why the results are different although I'm using the same calculation in both charts in Reporting Service & Alerting

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

Sam07 — Wed, 20 Nov 2024 13:33:57 GMT

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);

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

Lech_Miszkiewicz — Wed, 20 Nov 2024 13:44:24 GMT

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

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

Sam07 — Wed, 20 Nov 2024 14:31:05 GMT

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?

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

Lech_Miszkiewicz — Wed, 20 Nov 2024 23:58:43 GMT

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