topic Re: Set analysis help in QlikView

Set analysis help

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below issue.

I have used below expression in KPI

=COUNT({<[MonthName]={'$(=MonthName(Max([Date])))'},Product={'*'}-{' '}>}distinct id) getting 46 as Count

In a Report I have taken

MonthName as Dimension and

=COUNT({<[MonthName]={'$(=MonthName(Max([Date])))'},Product={'*'}-{' '}>}distinct id) as Measure

then getting

MonthName Measure

Mar 2018 44

---------------

When I have added another Dimension i.e., Product then we have 46 as Total

MonthName Product Measure

March 2018 Product1 32

March 2018 Product2 4

March 2018 Product3 3

March 2018 Product4 2

March 2018 Product5 2

March 2018 Product6 1

March 2018 Product7 1

March 2018 Product8 1

Total 46

In the above KPI I have to get 46 like above report instead of 44. Please help me to get 46 on kpi.

thanks in advance.

Re: Set analysis help

YoussefBelloum — Tue, 22 May 2018 08:23:23 GMT

Hi,

try this:

=Aggr(COUNT({<[MonthName]={'$(=MonthName(Max([Date])))'},Product={'*'}-{' '}>}distinct id),MonthName)

Re: Set analysis help

jonathandienst — Tue, 22 May 2018 08:35:44 GMT

Expressions with distinct are dependent ion the context and are not necessarily additive. The KPI will apply the distinct to the entire model. The table values will find distinct values in the month, but a value could appear in more than one month.

You can calculate a sum of rows value

=Sum(Aggr(Count({<MonthName = {"$(=MonthName(Max([Date])))"}, Product -= {' '}>} Distinct id), MonthName))

Re: Set analysis help

mahitham — Tue, 22 May 2018 08:49:49 GMT

Hi Jonathan,

Thanks for your reply.

After adding Productname to your expression working fine.

=Sum(Aggr(Count({<MonthName = {"$(=MonthName(Max([Date])))"}, Product -= {' '}>} Distinct id), MonthName,Productname))