topic Re: Match KPI value with table qlik in App Development

Match KPI value with table qlik

Aspiring_Developer — Thu, 30 Jul 2020 13:24:56 GMT

Hello All,

I Have to match the KPI value with the number of records in table

I have attached the extract of the file having number of records i wish to match with the KPi.

I am using below this expression in the KPI:

=Sum({<Nbr_KO, KPI_Code={'KPI104'},New_periode={"$(=Max(New_periode))"}>}Aggr(Count({<Nbr_KO>} Last_GR), Last_GR,Nbr_KO))

Here,as per attached excel , i should get 99 records in the KPI but the value is coming 108. this is the value for Jun.2020

When i select the Jun2020 from the month Filter is gives me the correct value i.e 99 . However in the expression i have already passed the set for maximum month.

The KPI should return the value for maximum monthyear as i have stated that in set analysis but it giving correct values when selecting Monthyear.

Please help.

Thanks in advance.

Re: Match KPI value with table qlik

Aspiring_Developer — Thu, 30 Jul 2020 13:27:26 GMT

Last_GR is date of Last good received in the attached excel

Re: Match KPI value with table qlik

sunny_talwar — Thu, 30 Jul 2020 13:47:02 GMT

What is New_periode and how does it relates to Month selection? Is Month?

Re: Match KPI value with table qlik

Kushal_Chawda — Thu, 30 Jul 2020 13:51:50 GMT

can you try formatting the date in set?

=Sum({<Nbr_KO, KPI_Code={'KPI104'},New_periode={"$(=date(Max(New_periode),'MMM YYYY'))"}>}Aggr(Count({<Nbr_KO>} Last_GR), Last_GR,Nbr_KO))

Re: Match KPI value with table qlik

Kushal_Chawda — Thu, 30 Jul 2020 13:54:35 GMT

also I think you will need max date set in inner aggregation

=Sum({<Nbr_KO, KPI_Code={'KPI104'},New_periode={"$(=date(Max(New_periode),'MMM YYYY'))"}>}Aggr(Count({<Nbr_KO,New_periode={"$(=date(Max(New_periode),'MMM YYYY'))"}>} Last_GR), Last_GR,Nbr_KO))

Re: Match KPI value with table qlik

sunny_talwar — Thu, 30 Jul 2020 13:57:18 GMT

Can try with MaxString too

New_periode = {"$(=MaxString(New_periode))"}

Re: Match KPI value with table qlik

Aspiring_Developer — Thu, 30 Jul 2020 15:05:05 GMT

@sunny_talwar

Periode is month year - Jun.2020

But when we tak max (Periode) it returns number .

So I created a new field at backend

Num (Periode) as New_Periode

And passed the same in set analysis

Re: Match KPI value with table qlik

sunny_talwar — Thu, 30 Jul 2020 15:13:01 GMT

What do you get when you use this

Count({<Nbr_KO, KPI_Code={'KPI104'}, New_periode = {"$(=Max(New_periode))"}>} Last_GR)

Re: Match KPI value with table qlik

Aspiring_Developer — Fri, 31 Jul 2020 05:35:33 GMT

@sunny_talwar

Hello,

Many thanks. The below for the data set except for one country(attached excel file):-

Count({<Nbr_KO, KPI_Code={'KPI104'}, New_periode = {"$(=Max(New_periode))"}>} Last_GR)

In the attached excel i have 475 records but in the KPI i see 372 records using above experession.

Please suggest.

Many thanks

Re: Match KPI value with table qlik

sunny_talwar — Fri, 31 Jul 2020 11:25:17 GMT

What is Last_GR in the Excel file? What is KPI_Code? I guess can you provide the mapping between your expression fields and fields from the Excel file?