Want to calculate the KPI values based on special conditiaon

Q&A
, ,
1

I want two kpis, one KPI will represent first KPI if ‘b’ is null then distinct count of column ‘a’ and anouther KPI I want which will represent if column ‘b’ is not null then distinct count of column ‘a’
but if you see column ‘a’ value ‘123’ has both ‘null’ and value ‘Date’ in column ‘b’ that case i dont want to count this in my first KPI because it has both ‘null’ and ‘date’

sample example,
|a |b|
|123|2/2/2024|
|123|null|
|24|2/2/2024|
|23|2/2/2024|

i want to count only the null.

expected output is

first KPI = 0
second KPI = 3

but my output is coming

first KPI = 1
second KPI = 3

2

Hello @Deepak1, my thought is instead of directly using field B, you could use a maxOver function to only return a single row for each ID in column A. Then you could filter the first KPI to only include NULLs. It would look something like this:
maxOver({b}, [{a}], PRE_AGG)

I think that will lead you to your desired solution. Let me know if you have any follow-up questions!

3

Thank you so much @DylanM for the quick response and it worked.

Have a great day Ahead!!

1 Like