I want to ask a question in Quicksight Q similar to this-
“Show me total sales per person within first 30 days of joining”
In my dataset, I have the following columns-
Member ID || Transaction Date || Sales || Date of Joining
Here, Date of Joining refers to the date on which the person joined the program. Thus, Quicksight Q will have to check if transaction date is within 30 days of Date of Joining column, then consider the Sales amount and sum it up per person.
I wanted to know if there is a way to do this in Quicksight Q, keeping in mind that the question can change from 30 days to 45 days or any other number ( Dynamic, something like a parameter in Quicksight Dashboards). Would appreciate any help possible on this.
@Sahil you might want to define a calculated field to include the logic of total sales within days of joining, something like this ifelse(dateDiff({Transaction Date}, addDateTime(30, ‘DD’, {Customer Join Date}, ‘DD’) <= 30, {Sales}, 0)
Thank you for your response, but my query is slightly different.
In the calculated field you’ve mentioned, the number 30 is hard-coded. However, I would need this to be passed as a variable, so that it can take values other than 30 from the prompt, as I’ve described in my question above.
So if I ask, show me transactions within 45 days of joining, then the same calculated field should give me the result by changing the 30 to 45 and so on.
@Sahil currently QuickSight Q doesn’t support passing variables to user questions, I will flag this as a feature request. As an workaround you can create multiple metrics one with 30 days and other with 45 days to use a range value while defining the calculation.
Let us know if defining multiple metrics strategy can help you in meeting your requirement.
