Hello! Newbie here, having hard time figuring out how I should approach my problem. I watched a lot of videos and tutorials but I’m still stuck.
So following is how my original file table looks like:
Title |Data | ID | Date |
next step |stage 1 | A | Jul-1 |
next step |stage 2 | A | Jul-3 |
status | closed | A | Jul-5 |
next step |stage 1 | B | Jul-4 |
next step |stage 5 | B | Jul-6 |
next step |stage 3 | C | Jul-2 |
There are unique IDs for each “requests”, which often go through stage 1 to 12. I need to create a dashboard that shows how many tickets are in each stage when the user adjusts dates. For example, when the user chooses July 3rd, it should show 1 count in stage 2 and 1 count in stage 3. If the user chooses July 5th, it should show 1 count in stage 1 and 1 count in stage 3, because the request from ID = A is now closed.
I am first stuck because 1) Even though I added date parameter, it’s not connected to the pivot table that has all the stages and counts 2) how to get the max(date) <= chosen date and choose the correct stage (for example, I don’t want to double count different stages under the same ID).
Suggestions, tips, anything would be helpful - thank you very much in advance
Hello @Soyoon , welcome to the QuickSight community!
For the first part of your question, you should be able to achieve this with an ifelse function, something along the same lines as this: countOver(ifelse({Date} = ${DateParam}, {ID}, NULL), [DATA], PRE_AGG)
For the second part: 1) You will need to link your parameter to your visual. You can do this in two way. First by applying it to a filter that targets your visual or, second, by applying the parameter to a calculated field like above and adding that to the field well of your visual. 2) It will be easier to work this out in an arena with your data but the above calculated field should start you on the path to make sure that you do not have duplicates.
Do you mind creating an arena link for this problem? This will help with troubleshooting:
So basically, when I choose a date like 7/20, I should get the number of tickets for each stage. However, if a ticket has gone through step1/2/3 and is at step3 by 7/20, it should only count towards step 3 only.
