I am working on a dashboard, using a pivot table and have multiple-wave data. I have a multiple-response question and a rating scale question to be shown side by side and have filtered it on a single wave, so i have created unpivot dataset for these questions. And I have used a calculated field ‘distinct_count(responseid)’ for counts however % calculation is a bit tricky part as for Q15 (has attribute list as response) % are to be calculated based on total for current wave but for Q16 (Rating scale question has attribute list as response for top 2) % is to be calculated based on Q15 numbers as base. Any help is appreciated. Used this calculated field but not working - ‘distinct_count(ResponseId)/sumOver(distinct_count(ResponseId),[WAVE])’
as Q15 calculation logic (calculate % base on total current wave) is different with Q16 (calculate % base on last wave - Q15), I think you need two different calculated fields.
Did you try using runningSum? I think it may help to calculate of last wave
Thanks for your response!
Let me rephrase the problem, there are 2 fields Q15 and Q16 (both have data present across the waves however I am only interested in current wave data and have applied wave filter accordingly).
Now if you look at the image I have provided 4 columns 2 for Q15 (Importance) Counts vs %s and 2 column for Q16 (Performance). Q15 %s are to be calculated based on total of 1488 however Q16 %s are to be calculated on Q15 counts.
So I guess runningsum wouldn’t give desired output.
thanks for the clarification, I have created 2 calculated fields. one of them using LAC-A Using level-aware calculations in Amazon QuickSight - Amazon QuickSight to calculate the % for Q15 over totall.
Another calculated field to compare between Q15 and Q16
Can see if it works? you may access here. 49714 analysis
1 Like
Thank you so much! This worked.
Although I have a related issue, %s are generated but the counts are appearing incorrectly. I have a main dataset and then two unpivot datasets for q15 and q16. Main dataset has 1488 records (unique) however when I join q15 and q16 (left join) with main dataset and try to generate pivot tables, it doesn’t give me correct counts for q15 response options (I assume records in main dataset are getting duplicated). If you could suggest a best practice to join datasets it would be awesome.
/.\