How to get difference value and the end-of-month values

Question & Answer
, , ,
1

Hello, I’m new to QuickSight! I’m learning how to use the function in QuickSight and I’m try to define the calculated fields. Now I have two problems.
sample
Could I define the calculated fields?

(1) Get difference value
I’d like to get the difference value from the start point to the target point.

For my sample data(01), my expected value is 45 (100-55=45).
I thought I could use the difference function, but I couldn’t get my expected one.
Difference - Amazon QuickSight

(2) Get the end-of-month values
I want to get the difference between the value at the end of the month two months ago and the one of the last month. If there is no value at the end of the month (which is null-value), I want to get the value closest to the end of the month.
Let me show my expect value more with my sample data(02).

If today would be Septemper 20, my expected value is 45 (100-90=10).
It is the value on July 20 minus the value on August 31.

If today would be October 20, my expected value is 5 (95-90=5).
It is the value on August 31 minus the value on September 5.

I thought I could use the maxover function, but I don’t know if I could do in QuickSight.
maxOver - Amazon QuickSight
I also checked the page.
How to get only the last records in quicksight? - Stack Overflow

If I would appreciate it if you could kindly give me advice for one of them.

2

Hi @vivi,

for me it is always a good way to do it in different steps.
so calculate first and last and calculate the difference.

Did you try

and

BG

3

Hi, @ErikG
Thank you very much for your quick reply!
As my sample data is not broken down by month, I don’t know how to get the value of the last month with firstValue and lastValue. I am also concerned if I could calculate with dynamic values since the calculations are based on today’s starting point.
I’m sorry for my slow understanding, but I would be glad if you would give me information…

4

Hello @vivi,

In regards to your first question, you said you did not get the expected value, so what did you get? Something that could impact the output you are receiving would be how you are displaying the calculated field. A calculation as simple as {start_point} - {target_point} should give you the result you want if it is displayed in a table with a row for each start_date, but would aggregate differently (sum or average) if displayed in a KPI.

Try creating a calculated field that that uses lastValue with a partition field converting your date field into months using truncDate. It would look something like this:

lastValue(
    [{points}],
    [{date} Desc],
    [
    	truncDate('MM', {date})
    ]
)

Okay, now make 2 more calculated fields to get the last month value and the value from 2 months ago using addDateTime in an ifelse:
ifelse(addDateTime(-1, 'MM', now()) = truncDate('MM', {date}), {lastValueCalc}, NULL)
ifelse(addDateTime(-2, 'MM', now()) = truncDate('MM', {date}), {lastValueCalc}, NULL)

Now get the difference of the 2 calculated fields above. This should guide you to the solution you are expecting!