Count distinct with group by in the script

EISSA — Mon, 21 Mar 2022 20:45:59 GMT

Hello,

below is a sample of what I am trying to do to count distinct two fields using the group by.

First I know that this is very easy to be done in the chart side and not in the script but I am obliged to do it from the script because I have billions of record that makes my dashboard very huge and slow in performance and in my current dashboard all I want is the aggregation grouped by the dimensions below and if I am not doing any filter using below dimension to get the total of these 2 kpis and to be distinct.

For example the below result should show :

[KPI Value Customer] = 2 (C1 and C2)

[KPI Value Player Days] = 5 (P101 ,P102 ,P103 , P201 ,P202 )

and let's say in the dashboard we filtered brand = 3 the KPIs should show

[KPI Value Customer] = 2 (C1 and C2)

[KPI Value Player Days] = 3 (P103 , P201 ,P202 )

testTable:
LOAD * INLINE [
year , Datemk , customer , playerdays , brand , provider , customermarket
2022 ,01032022, C1 ,   P101 , 1 , pr1 , cust1 ,
2022 ,02032022, C1 ,   P102 , 2 , pr2 , cust1 ,
2022 ,03032022, C1 ,   P103 , 3 , pr3 , cust2 ,
2022 ,03032022, C2 ,   P201 , 3 , pr3 , cust2 ,
2022 ,04032022, C2 ,   P202 , 3 , pr3 , cust2 ,
];

GroupingTable:
Load brand,year,customermarket,provider, kpiname,
'MTD' as [Period],
count(DISTINCT (playerdays)) as [KPI Value Player Days] ,
count(DISTINCT (customer)) as [KPI Value Customer]
resident testTable
group by brand,year,customermarket,provider;

Drop Table testTable;

Thanks in advance for your support and suggestions.

Re: Count distinct with group by in the script

rwunderlich — Mon, 21 Mar 2022 22:58:52 GMT

Your script looks like it should work. Except there should be no () around the fieldname in Count.

count(DISTINCT playerdays)

-Rob

topic Count distinct with group by in the script in App Development

Count distinct with group by in the script

Re: Count distinct with group by in the script