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.