Dynamic calculations on visual level

Question & Answer
, ,
1

Hi,

We currently have a calculated field that performs the following:

#This is an expression of the average margin in percentage the month that ends on endM1
#This could for example be 50% for the month of March if the endM1 parameter is set to March 31st 2023

((sumIf({margin},{date} >= truncDate(‘MM’, ${endM1}) AND {date} < truncDate(‘MM’, addDateTime(1, ‘MM’, ${endM1}))) /
sumIf({revenue},{date} >= truncDate(‘MM’, ${endM1}) AND {date} < truncDate(‘MM’, addDateTime(1, ‘MM’, ${endM1}))))

/

#This is an expression of the average margin in percentage for the full year prior to the endM1 date.
#This would in the same example as above be then the margin in % for the full year of 2023. (If endM1 is set to Match 31st 2023).

(sumIf({margin},{date} >= truncDate(‘YYYY’, addDateTime(-1, ‘YYYY’, ${endM1})) AND {date} < truncDate(‘YYYY’, ${endM1})) /
sumIf({revenue},{date} >= truncDate(‘YYYY’, addDateTime(-1, ‘YYYY’, ${endM1})) AND {date} < truncDate(‘YYYY’, ${endM1})))) - 1

This works well and as intended and shows whether there has been a margin growth compared to the previous year.

However, the question is: would one in quicksight be able to show this in a bar chart over time? So that each month in the chart executes this type of calculation dynamically compared to the month in the chart field (e.g. January 2023, February 2023, March 2023, and so on.). In this case, all of them should calculate the margin growth in (%) against the entire previous year. I’ve also added just an illustrative image of how this would be calculated and what should display.

image
image1372×772 120 KB

Perhaps there is some way using table calculations but we’ve been unable to find a way here. Any help is appreciated.

On this note, it seems a great thing in calculated fields would be the ability to access a visuals field well values. For example dates in the topic above being able to dynamically write the expected outcome yourself.

2

Hey @DanielJansson !

I would recommend breaking these up into two separate visuals and having them next to each other. You could remove the Y-axis title and grid labels of the right side visual, and using free form layer them next to each other to create the effect of a single visual.

3

Hi @duncan,

Thanks for your input.

I might really be misinterpreting your answer but it really doesn’t make sense to me how that would work. The question here is not about one or two visuals but rather the dynamic calculation depending on month of a KPI that always has an established baseline using the same period. Whereas e.g. table calculations allow for a month over month metric for every month this would actually display every month in the visual compared to the average margin in the previous year.

Clarifying here: the “Illustration of calculation method” in my initial post was just a way of visualizing the formula described above. The blue text in the denominator of 45% refers to referencing the full year prior (AVERAGE = 45%) and the green text is the average margin in the month in question (i.e. jan/feb/mar and so on). The actual visual output is very simple, it’s what is under “what it would display like in quicksight”.

Best regards,
Daniel

4

Hey @DanielJansson !

The best way I can see you creating this calculated field is by wrapping it in an ifelse() that determines the date and then runs your existing calculation. For example, the logic would be "if the year date part of ${M1} is equal to the year date part of Now() then run the existing calculation, else (margin by month/ avg margin of previous year). Then filter your visual using relative date filters set to current and previous year or previous N years with a 1 year look back window.

However the reason I suggested two visuals formatted together is because you are using sumif() functions which would most likely cause nesting errors. It would probably be easier to break up the visual than totally adapt your current calculation.

You can also share this as an arena and we can work through the calculation there.

5

Hi again @duncan,

I still don’t think that this can solve the issue. Now() returns the datetime of the SPICE database server which is not relevant to the topic in question.

I’ll rephrase the overall question which I believe is the key to this whole topic. Is it possible in a calculated field to access the individual date of the visual element. I.e. what the X-axis says below each bar (if the x-axis is a datefield)?

/Daniel

6

Hello @DanielJansson, I was able to put together a work-around solution for your question. You can view the bar chart visual I created here:
Dynamic calculations on visual level

My fields are not the same as yours, but the calculated fields I made to create the visual can be updated with the fields in your dataset. This will create a group (or a bar in this case) for the year prior to the endM1 date, and then will display each month during the year that matches with the endM1 date.

I will mark this response as the solution, but let me know if you have any follow-up questions. Thank you!

1 Like
7

Hi @DylanM,

Thanks for doing this. It’s however not really the entire way there. The visual you are showing displays the average margin for the month if its in the same year as endM1 or the average margin for the full year if it’s the year prior to endM1.

This is basically achievable in a very simple way with two calculated column called e.g.

Profit (%) = sum(Profit) / sum(Sales)

and a column called date which is:

Date =

ifelse(

truncDate(“YYYY”, {Order Date}) = truncDate(“YYYY”, ${endM1}),

toString(truncDate(“MM”,{Order Date})),

ifelse(

addDateTime(-1, “YYYY”, truncDate(“YYYY”, ${endM1})) <= {Order Date},

“0. Full year prior”,

NULL

))

And then adding these two as x / y dimension to a bar chart.

This was as I described just an intermediary step. You now want to divide all of the values you are seeing in this chart (in the same year as endM1) with the value that you are displaying for Full Year prior. For example, in the data you sent you want to divide 16.12% (the value for January) with 13.43% (the value for the full year prior). This gives you what the margin growth has been in %.

So the output you are looking to achieve is (for the first two months):

January = (16.12% / 13.43%) - 1 = 20.03%

February = (7.91% / 13.43%) - 1 = -41.10%

This is where the problem arises from my point of view. What you want to do is do the following calculation:

(sum(Profit) / sum(Sales)) / ((sumIf(Profit, date=“0. Full year prior”) / sumIf(Sales, date=“0. Full year prior”)))

But this will when you add it to a visual always be 0 for all the months except for “0. Full year prior” where it is exactly 1 (since it’s dividing by itself) because of this calculation happening after the filters from the visual apply to the dataset.

Any thoughts or workarounds?

1 Like
8

Hello @DanielJansson, I was able to build a work-around. The calculations are going to get significantly more complicated though, because we will need to build each individual group manually. You can find this version of my solution here:

Dynamic Calculations Update

If you want a better understanding of how/why I created those groups using a denseRank calculation on Order IDs, you can find more information from my webinar:

Removing the filters from the visual directly, created these static groups from the denseRank field, and then using nested ifelse statements inside of LAC-W sumOver aggregations, will allow you to compare the current month to the previous year.

This should resolve most of the issues you are facing. Let me know if you have any other questions. Thank you!

1 Like
9

Hi again @DylanM.

Big thanks for taking the time to dive into this type of topic, sincerely appreciate it.

I indeed agree that this gets fairly complicated and really a way to almost “break” quicksight since custom aggregation tables or a way to structure calculations isn’t possible apart from on row-level.

I also assume that this can have fairly problematic scaling behaviors. Since you are essentially partitioning the data in 13 sets and then arbitrarily using the rows thereof to calculate one or multiple fields, and then taking the min value of these I assume that this might become problematic if you have millions of sales.

Thanks again,
Daniel

PS. some minor perhaps suggestions just to add something of value to this discussion is that you could use dates in the “Date Groups” calculated field. e.g. such as this:

{Rank Orders} <= (maxOver({Rank Orders}, , PRE_AGG)*(1/13)), parseDate(“1970-01-01”),

{Rank Orders} > (maxOver({Rank Orders}, , PRE_AGG)*(1/13)) AND

{Rank Orders} <= (maxOver({Rank Orders}, , PRE_AGG)*(2/13)), truncDate(“YYYY”,${endM1}),

{Rank Orders} > (maxOver({Rank Orders}, , PRE_AGG)*(2/13)) AND

{Rank Orders} <= (maxOver({Rank Orders}, , PRE_AGG)*(3/13)), addDateTime(1, ‘MM’, truncDate(“YYYY”,${endM1})),

This allows you to later use builtin features on formatting dates etc. and also add filters to show only those that are before endM1.