Does anyone know how to calculate semi-additive measures in QuickSight? These are measures, like a balance, that can be summed across everything but date/time. I thought it would be possible to create a max date calculation using maxover() then using the result in a sumif.
MaxDate = maxOver({Date},[{Date}],PRE_AGG)
sumif(Date = MaxDate…
Hey @Keith_Henkel !
Can you try this:
ifelse({Date}={MaxDate}, sumOver({balance}, [], PRE_AGG), 0)
Let me know if this helps!
Thanks for the quick reply Dunca. I’m actually having problems with calculating the MaxDate using maxOver(). The column I am using is a date. If I go to one of the examples in demo central, I can create a maximum date by using maxOver({Date},[{Date}],PRE_AGG) just fine. When I try this in my trial account I get the error text at in the calculation window. I am using direct query for testing.
Hi @Keith_Henkel,
Would you be able to share the error text you are seeing?
I’ve tried to build an example analysis to replicate your situation using a simple dataset containing a list of dates called “Start Date” and a generic measure called “Duration (days)”.
I then built a MaxDate calculated field as follows:
maxOver({Start Date},[truncDate("WK",{Start Date})],PRE_AGG)
(using truncDate to partition into weeks).
I then built a calculated field called sumif for the sumif calculation:
sumif({Duration (days)},{Start Date}=MaxDate)
Adding these to a table visual gives me the following:
Is this the kind of result you are hoping to achieve? (Noting that my data only contains one row per day, so the sumif equals the Duration when the dates are equal). If not, perhaps you can provide more details on the expected output.
Many Thanks,
Andrew
I got the MaxDate calculation to work (maxOver(FullDate,[FullDate],PRE_AGG)) but I don’t think this is what I need. I’m trying to set up a calculation for semi-additive behaviour. The ideal result is a calculation that automatically shows the value for the last date in a period based on the level of aggregation in the visual. For example, if the visual has a date column set to daily aggregation, it would just show the daily value from the underlying data. If the aggregation is set to monthly, it would show the last day for each month (the maximum date in the month). The trick in most BI tools is to know the level of data aggregation on a visual. Is there a typical pattern for this in QuickSight?
Hello @Keith_Henkel !
Thank you for providing more details! For your use case do you want the data for the most current date aggregation period (i.e. today, this week, this month), or do you want to use this to be able to look back at a specific day/week, for example in August, or see the values of fields on the final day of a specified month?
You could try creating parameters such as “MaxDateDay” that are set up like this screenshout:
Then, create a Parameter control list for the date aggregates you want, something like this:
Then create a calculated field like this:
ifelse(${PeriodChoice1} = 'Day',${MaxDateDay},
${PeriodChoice1} = 'Week', ${MaxDateWeek},
${PeriodChoice1} = 'Month', ${MaxDateMonth}, {Order Date})
My thinking here is that you would also need to add a control or filter to the sheet that would control the date range/date aggregation you want to drill down to.
Let me know if this is the direction that you are looking to go!
Thanks for the quick response on this! Our date dimension has a number of boolean columns for end of week, end of month, etc. So, we can create calculations using these columns or the maxdate method. We would like a sem iadditive calculation that works at any date level without a user having to set a parameter. For example, when a user drills down from year to month to week, the same calculation would work. We use Looker today. The calculation in Looker can peek at the date level:
if date level in query = year then EndOfYearBalance
if date level in query = month then EndOfMonthBalance
etc.
These are awkward to to set up, but they work. Power BI allows you to use a max date approach to find the maximum date in a group. If the visualization is at a year level, it finds the last date in the year. Then you use a calculation like this: sumif(date = maxdate, Balance)
We are trying to get away from Looker and don’t want to use Power BI so I was hoping QuickSight can support a similar approach without making the user pick a date level.
Hello @Keith_Henkel!
I will mark this as a feature request for the Quicksight team!