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.
