“Breakdown of number of trips on daily, weekly and monthly basis”. how to do this in quicksight , I have data with trip_date and trip_id columns, i want user to select daily, weekly and monthly basis, then based on the selection display the number of trips.
For example, if user selects year=‘2024’, then user will have 3 options daily, weekly, monthly.
If user selects weekly, then show weekly count of number of trips.
Please help me how to do this.
Hello @Sainath, you can make this work with a mix of LAC-W calculations and an ifelse statement that utilizes a parameter value. You can create control linked to a parameter that will display the 3 options you want the user to select from. Make sure you provide a default value to the parameter so it can be used in the calculated field.
Now the one thing I want to confirm is, are you going to be returning an average for daily, weekly, or monthly from the start of 2024? That is what I will build my calculations for, but let me know if you are expecting a different result.
Next create your ifelse statement:
Trip Date by Selection =
ifelse(
${DateType} = 'Daily', truncDate('DD', {trip_date}),
${DateType} = 'Weekly', truncDate('WK', {trip_date}),
${DateType} = 'Monthly', truncDate('MM', {trip_date}),
NULL
)
Now we can use that in the partition field of our calculated field.
Trips by Selection = avgOver(distinctCountOver({trip_id}, [{Trip Date by Selection}], PRE_AGG), [], PRE_AGG)
This will get the total of trips per date type selected, then get the average over the course of the time horizon selected. I will mark this as the solution, but please let me know if you have any remaining questions.
Hi @DylanM , Thanks for your reply.
I want to count total number of trips per week or month or daily based on user selection.
My data is coming for redshift materialized view contains two columns: date and trip_id.
I created two calculated fields and a parameter as said.
But now, how can a create a visualization using this calculated fields and parameters. I’m getting error, please see all the SS and help me.
Trip Date by Selection calculated field:
ifelse(
${DateType} = ‘Daily’, truncDate(‘DD’, date),
${DateType} = ‘Weekly’, truncDate(‘WK’, date),
${DateType} = ‘Monthly’, truncDate(‘MM’, date),
NULL
)
Trip date ny selection calculated field:
ifelse(
${DateType} = ‘Daily’, truncDate(‘DD’, date),
${DateType} = ‘Weekly’, truncDate(‘WK’, date),
${DateType} = ‘Monthly’, truncDate(‘MM’, date),
NULL
)
parameter:
Use parameters to dynamically control values in your fields, filters, and sheet.
Name
Data type (Not alterable after creation)
String
Values (Not alterable after creation)
Single value
Static default value.
**the error I’m getting : **
Error details
|region:|us-east-1|
| — | — |
|timestamp:|1715894065798|
|requestId:|31deb919-4473-444a-97f5-59fb3fd6fc34|
|sourceErrorCode:|DISTINCTCOUNTOVER_INVALID_ARGUMENT|
|sourceType:|REDSHIFT|
In the above reply, I didn’t added another calculated field.
And also, I want user to select year first and then selection like month, week, dayely. I will create a filter for year from the date column.
Trips by Selection calculated filed:
avgOver(distinctCountOver(human, [{Trip Date by Selection}], PRE_AGG), , PRE_AGG)
Hello @Sainath, I am curious why you are receiving a redshift related error here. Is the dataset that is connected to your analysis ingested into SPICE or on Direct Query?
Also, if you wanted user selection to manage the year as well, you would need to add another check into the calculated field. It would look something like this:
ifelse(
${DateType} = ‘Daily’ AND ${Year} = extract('YYYY', {date}), truncDate(‘DD’, date),
${DateType} = ‘Weekly’ AND ${Year} = extract('YYYY', {date}), truncDate(‘WK’, date),
${DateType} = ‘Monthly’ AND ${Year} = extract('YYYY', {date}), truncDate(‘MM’, date),
NULL
)
Hi Dylan, its a direct query.