Find the distinct count using count if

sudms — Fri, 20 Aug 2021 13:48:02 GMT

Hello,

I am trying to get the total distinct count from below table.

Here if you see I have two conditions like

1> SubCat should not be null

2> Category and SubCat should not match

With this I want the single value in my KPI of distinct count 5. I tried with below query but it gives 6 for the Aggr.

Sum(Aggr(IF(Len(SubCat)>0 and SubCat<>Category, count(distinct ID)),SubCat,Category))

Any solution to get the answer.

Re: Find the distinct count using count if

Kushal_Chawda — Fri, 20 Aug 2021 14:16:25 GMT

@sudms best way to do it is create a flag in script for the required conditions and use that flag in expression to get the count

Data:
LOAD Category,
SubCat,
if(len(trim(SubCat))>0 and SubCat<>Category,1,0) as Flag,
ID
FROM Table;

Now, create a expression like below

=Count({<Flag={1}>}DISTINCT ID)