Showing year to date sum without showing all rows

Question & Answer
, ,
1

I have a data set that looks like this:

  • EndOfMonthDate (1/31/2023, 2/28/2023, etc in a date format)
  • Various String Filters to categorize transactions
  • Transaction_Count (it’s the number of transactions in that month).

I’m trying to come up with a Calculated Field that does the following, in the following conditions:

  1. It sums up the Calculation between 1/1/2023 and the value of EndOfMonthDt for that row, filtered appropriately across all of the strings that categorize transactions
  2. AND it also will continue to be accurate even if I filter out earlier rows in the month.

So I think that this satisfies part 1: periodToDateSumOverTime(sum({COUNT_APP}),ENDOFMONTHDT,YEAR)

However, I really only want to show the most current EndOfMonthDt in the dataset, and whenever I do that, the calculation “loses” the previous dates, i.e. if I Filter out 1/31/2023, year to date will only calculate Feb and March together. I understand why it does that, but I want it to not do that.

I am thinking maybe the PeriodToDAteSum MIGHT work but the examples aren’t very clear, so I’m having difficulty figuring out the difference between the over time and not over time functions.

2

For Dynamic YTD Value, please try below steps

  1. Create parameter ( $Uptodate), select fixed date & make it dynamic
  2. Create Calculated field YTD

YTD = ifelse(dateDiff({order_date},${AsOfDate},“YYYY”) = 0 AND {order_date}<=${AsOfDate}, sales, 0)
3) Drag YTD to Row pill
4) Select any date from parameter ( We will get YTD data as per the selected date)

YTD value will be in Row

1 Like
3

So, if I am understanding right, your solution would have a control where you set a value for UpToDate with a control, and then it would just always calculate to the date you select.

Unfortunately that won’t meet my need.

I guess what I really want is for PeriodToDateSumOverTime to have a level aware option so I can pick pre-filter.

4

Hello @TRube The sample YTD calculation for this requirement would be something like below. You could start with this and play with filters and iterate on the solution. Please let us know if this has resolved your question.

sumOver
(
ifelse( (EndOfMonthDate>=truncDate(‘YYYY’, EndOfMonthDate) and EndOfMonthDate<now()),{Transaction_Count},0)
,
[ filter1,filter2],
PRE_FILTER
)

Also refer Level Aware documentation at this location for future use: Using level-aware calculations in Amazon QuickSight - Amazon QuickSight

2 Likes
5

Thanks. I’ve tried to understand the Level Aware Calculations and the one about periodtodate a few times and still don’t really get it.

It would be nice if they would explain these concepts in a more easy to understand fashion somewhere.

6

@TRube Follow this blog to get some hands on. Hope this helps Create advanced insights using Level Aware Aggregations in Amazon QuickSight | AWS Big Data Blog

2 Likes