I have a pivot table like below, I wanted to calculate the percentage of the “sign_up” column over “_first_open” column, how can I do it? tried to create a calculation field with "distinct_countIf(count,{event_name}=‘sign_up’) /distinct_countIf(count,{event_name}=‘_first_open’) " and added to the Value of pivot table, but did not work.
Hi @luorobin,
do you get a error msg?
BR
1 Like
@ErikG If I remove the event_name as the second-level of group, it works, but I want to keep the second-level grouping, if there any way to do it?
Hello @luorobin, I am thinking if you tried to break this up a little more that you may have more success. Try creating 2 calculated fields with ifelse statements:
signUps = ifelse({event_name} = 'sign_up', 1, 0)
firstOpens = ifelse({event_name} = '_first_open', 1, 0)
Then you can try this first: {signUps}/{firstOpens}
This might work, but based on the look of your dataset, if this still gives incorrect values since I am assuming those 2 items do not exist on the same row, you can try this:
sumOver({signUps}, [{country}, {date}], PRE_AGG)/sumOver({firstOpens}, [{country}, {date}], PRE_AGG)
That might have more luck handling the data across multiple rows, and grouping them as expected! I will mark this response as the solution, but let me know if you run into any issues with implementation.