Generate Single KPI Value from Calculated Fields

Q&A
, ,
1

I have a data table like the one below and I’m trying to create single KPI card visuals that would be the average of the first_period for event_type = purchase_transaction and I’m at a loss on how to do that.

I created these calculations which make me think I’m on the right track as I get the value I want but I don’t know how to extract it just as a scalar for a kpi visual.

First Event = minOver(min({days_since_subscription_start}),[{customer_id},{event_type}])
Average First Event Period = avgOver({First Event})

customer_id event_type event first_period
cus_F3rX42mDPfiy2e purchase_transaction Export 1
cus_F3rX42mDPfiy2e feature_usage login 1
cus_F3rX42mDPfiy2e feature_usage Created List 1
cus_F3rX42mDPfiy2e feature_usage Added to List 2
cus_F3rX42mDPfiy2e feature_usage Viewed Profile 9
cus_F3reCIEU12nM3x purchase_transaction Document Image 1
cus_F3reCIEU12nM3x feature_usage Viewed Profile 1
cus_F3reCIEU12nM3x feature_usage login 1
cus_F3rlTVpRk2N2fl purchase_transaction Document Image 1
cus_F3rlTVpRk2N2fl feature_usage login 1
cus_F3rlTVpRk2N2fl feature_usage Viewed Profile 1
cus_F3rlTVpRk2N2fl feature_usage Printed Report 18
cus_F3rlTVpRk2N2fl purchase_transaction Criteria Bot 29
2

Hi @mjproperty,

What value should your KPI card show for your sample data? purchase_transaction appears twice for your 3rd customer. Do you want to take min of 1 and 29?

3

It should be 9.11 which is the average value of the minimum of first_period per customer where event_type = purchase_transaction

4

Based on what you said, the correct calculated field should be:

avg(
    min(
        ifelse({event_type} = 'purchase_transaction', {first_period}, null),
        [{customer_id}]
    )
)

I don’t know why the expected result is 9.11 though. The minimum of first_period per customer where event_type = purchase_transaction is 1 for each of the 3 customers. If you then take the average, that should give you 1. Can you please clarify?

image
image1344×944 105 KB

5

Sorry I was unclear and I misinterpreted your question, I only included a small snippet of the dataset so you could see the structure. 9.11 is the average for the entire dataset.

6

If the correct result for the sample data that you provided is 1, the above calculated field should be correct. Please try it.

7

Thank you David, it worked!