I am trying to build a single line chart which have both quarterly and YTD data.
So on March 13, 2024 the line chart should show,
In this case YTD and Q1 2024 is same, but in April the dashboard will be updates to surface Q1 2024, Q2 2024, and YTD.
Any suggestion on how do so. My chart have multiple “values”
Hello @Karan_Chhabra, we can definitely accomplish this, one thing I am curious about though. Do you want each of the Quarters you are displaying to have their own line, or be shown on a single line?
If the Quarters are on the same line and YTD on another, you would want 2 calculated fields added to your line chart. Then, how we would build the final function, would depend on how you want the x-axis to display in your visual. I’ll provide some suggestions and we can go from there.
Values for YTD = ifelse(truncDate('YYYY', now()) = truncDate('YYYY', {date}), {value}, NULL)
Values for Last 2 Quarters = ifelse(truncDate('Q', now()) >= truncDate('Q', {date}) AND addDateTime(-1, 'Q', truncDate('Q', now())) <= truncDate('Q', {date}), {value}, NULL)
Both of these functions will dynamically return the values based on the current year, and based on this Quarter and Last.
Now you can aggregate the outputs of each of those functions how you want for the visual display. Either in a calculated field or you can change the aggregation in the field well of the visual. If you want to split up the results for Quarters, you would just need 2 calculated fields instead of one. Each to return dates based on a specific Quarter of the 2. Let me know if this helps!
Hi @DylanM, Attaching a screenshot for clarify (note: Q1 2024 should be equal to YTD, the screenshot didn’t capture that). I understand how to extract quarterly values and YTD, but how do I create a reporting level for this, since YTD will include previous quarters as well.
Hello @Karan_Chhabra, thank you for providing some more information. So due to the constraints of the line graph, something like this will not exactly be possible. In order to manage the x-axis by a date of some kind, the date types will need to match in order to display the data. Now, something you could do would be to have your x-axis be set to a calculated field and based on a user selection display either Q2 23 and Q1 24 or YTD, and basically allow them to switch between 1 or the other.
You would want to create a parameter with a default value of something like YTD or Quarters, and just make it a string value. Then, create a control dropdown and manually enter the 2 options you would want the user to see. Now, create a calculated field to determine the values returned based on the user selection:
YTD or Last 2 Quarters = ifelse(${dateTypeParameter} = 'YTD' AND truncDate('YYYY', now()) = truncDate('YYYY', {date}), truncDate('YYYY', {date}), ${dateTypeParameter} = 'Last 2 Quarters' AND truncDate('Q', now()) >= truncDate('Q', {date}) AND addDateTime(-1, 'Q', truncDate('Q', now())) <= truncDate('Q', {date}), truncDate('Q', {date}), NULL)
With this calculated field set as your x-axis, the user could switch between viewing the data as the last 2 quarters or YTD. I would recommend changing how you would return the values for YTD, showing a single year on the line graph might not display well, so maybe you would want to return values as truncDate(‘WK’, {date}) or truncDate(‘MM’, {date}) instead to show more partitions. This should be the best way to get closer to your expected output though. I hope this helps!
@DylanM Hi Dylan, I want to surface all three on the same graph like the above image. I don’t want to toggle between YTD and last 2 quarters.
Hello @Karan_Chhabra, if you wanted to show it with all 3 periods on the same visual, you would likely need to look at doing something like a clustered bar chart combo or a bar chart. You could create groupings of data and then put those in the x-axis, but they wouldn’t show overlapping like in the example visual you posted above. The problem with your desired output is that the line graph in QuickSight will not allow you to show multiple date aggregation types on the x-axis, so that exact solution is not currently available. The work-around I provided above is the closest way I can think to achieve a version of your expected visualization.