Match KPI value with table qlik

[Aspiring_Developer]

@sunny_talwar 

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.

[Aspiring_Developer]

Last_GR is date of Last good received in the attached excel

[sunny_talwar]

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

[Kushal_Chawda]

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

[Kushal_Chawda]

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

[sunny_talwar]

Can try with MaxString too

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

[Aspiring_Developer]

• @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

[sunny_talwar · Accepted solution]

What do you get when you use this

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

[Aspiring_Developer]

@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

[sunny_talwar]

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?