Hello everyone,
I have 2 visuals in form of pivot tables (dates as Columns, strings as Rows and a calculated field returning a numeric value as Value) . Both visuals are using the same dataset. The only difference is that the first visual is filtered by a certain month (therefore it has a single column) and the second visual is not filtered (therefore showing dailies).
I would like to add a third visual, similar to the other two, where the Values are the difference between the Values in the previous visuals. (ex: Monthly value - Daily value).
Thank you
Hello @Ales, welcome to the QuickSight Community! Do you already have a value in your dataset that returns the date as month as well as date as day? If not create a calculated field to get your date month, it will look like this: truncDate('MM', {Date})
Now you can create another calculated field to get the sum of the Values by Date Month, it would look like this:
Month Value - sumOver({Value}, [{Date Month}], PRE_AGG)
Now you can grab your daily values with this calculation:
Daily Value - sumOver({Value}, [{Date}], PRE_AGG)
Then to get the field that you want, you need one final calculation:
{Month Value} - {Daily Value}
That should give you the value you are looking for!
Hello @DylanM , thank you very much for your reply!
I can create the {Date_Month} calculated field without problems but when trying to save the {Month Value}, I’m getting the error: For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.
This is my {Value}:
(
ifelse(
isnull(sumIf({count},{grouping} = ‘A’)),
0,
sumIf({count},{grouping} = ‘A’)
) +
ifelse(
isnull(sumIf({count},{grouping} = ‘B’)),
0,
sumIf({count},{grouping} = ‘B’)
)
) / sumOver(sum({count}), [{date}])
Hello @Ales, in that above statement trying to utilize the sumIf functions with the sumOver function will cause the mismatch aggregation error. We will have to find a different way to get that solution.
Are you trying to get the percent of the monthly value by day?
What you could try is splitting this into 2 fields.
Try this as you ifelse statement:
ifelse({grouping} = ‘A’ AND {count} > 0, {count}, 0) + ifelse({grouping} = ‘B’ AND {count} > 0, {count}, 0)
Then this field for your result: sumOver({Above Calc}, , PRE_AGG)/sumOver({count}, [{date}], PRE_AGG)
Thank you @DylanM,
I’m trying to get the percent of the values by months and cities in the first visual. In the second visual I’m trying to get the percent of the values by days and cities, along with the difference ( daily values - values of a certain month). Something like in the below screenshot:
For the field with the ifelse statement (calling it Views) I’m using:
ifelse({grouping} = 'A' AND {count} > 0, {count}, 0)
+
ifelse({grouping} = 'B' AND {count} > 0, {count}, 0)
For the monthly field I’m using:
sumOver({Views}, [{Date Month}, city], PRE_AGG) / sumOver({count}, [{Date Month}], PRE_AGG)
For the daily field I’m using:
sumOver({Views},[{date},city] , PRE_AGG)/sumOver({count}, [{date}], PRE_AGG)
For the difference between Monthly and Daily I’m using:
{Daily} - {Monthly}
But the difference calculated field doesn’t return the result I’m expecting.
Thank you very much!
Hello @Ales, this is my fault, maybe you should try isNull({count}) = ‘false’ instead of {count} > 0 in your ifelse statement. I think that might fix the problem.
Hello @Ales, did the update to the solution I proposed above resolve the issue you were facing in QuickSight? If so feel free to mark my response as the solution, or provide more information so I can help you further!
Thank you @DylanM ,
unfortunately it didn’t work.
It seem like it’s not possible to do a “Month over Day” or something similar in QS.
Hello @Ales, sorry for my delayed response to your previous message! There is not a function built in to QuickSight that automcatically calculates the value you are looking for. We may be able to tweak the suggestion I mentioned previously to make it work for the solution you are looking for. Also, you may have better luck if you set up the calculations in your SQL when you build your dataset to return the monthly and daily values on each row, then you could use quicksight calculated fields to build the simple subtraction statement that you mentioned above to get the difference between the 2.
1 Like