Revise the visual table to add the periodic target number

[Junny]

Hi,
Would you help me how to simply add Budget/Target number in Weekly or Monthly basis in visual table? It can be changed/updated how I aggregate Sales actuals.

Original dataset doesn’t have any target fields/number, so I want to add target column in visual table on the far right. Monthly, weekly target should has seasonality (not fixed number over time as I assume our business will grow). Can I revise visual like that? Or, making the calculated field is better way?

My question sounds easy, and I tried & still searching tips…but I couldn’t find any tips, Demo from the community posts, so please help.

FYI, Attachment is my poor trial, with just adding the Calculated field named the Target Sales with functions “abs (80)”, it doesn’t make sense.

Thanks

[duncan]

Hello @Junny , welcome to the Quicksight community!

have you tried the solution provided here?:

Dynamically change the seasonality target value

I have the below table. For each of the customer we have multiple SKU sales value (Product A sales values, Product B sales value etc.). Based on the monthly target, each product sales value field for each customer will show as green, yellow, or red. Since I have more than two columns, I have used a table with conditional formatting, instead of heat map. In addition, I am maintaining a calculated field to set the target value. [Screenshot 2023-02-10 at 10.27.47 AM] Now we have to manually upda…

[Junny]

Hi Duncan,
Many thanks for your advice. I will look at it, in the meantime can you advise how can I add monthly/yearly (periodic) targets in the visual mannually? I don’t want to put the same number in all periods as I captured in my post.

Best regards,

[duncan]

Hello @Junny ,

Could you try the calculation below and let me know what happens?:

80 + ifelse(dateDiff({Order Date}, now(), [period]) => 1, 10 
OR dateDiff({Order Date}, now(), [period]) => 2, 20,
OR dateDiff({Order Date}, now(), [period]) => 3, 30,
OR dateDiff({Order Date}, now(), [period]) => 4, 40,
0)

You will probably need the field that I have marked as {OrderDate} to be a date time Parameter so that the calculation is based on a static date.

For the [period], I have based this on Quarters which is why I have => 1 - 4, but you could switch it for any valid Quicksight date format.

dateDiff - Amazon QuickSight

[Junny]

Hi @duncan,
Many thanks! Thanks to you, I tweaked a bit, and found out below funtion works well for creating Monthly target for CY23 in a calculated field, and Weekly target for CY23. I can make a seperate calculated field for CY24 as well. Happy to get more feedback if you have a better way.

Field name: Target Sales Monthly (CY23)

ifelse(extract(“MM”, {Deal - Won time}) = 1, 150,
extract(“MM”, {Deal - Won time}) = 2, 200,
extract(“MM”, {Deal - Won time}) = 3, 250,
extract(“MM”, {Deal - Won time}) = 4, 200,
extract(“MM”, {Deal - Won time}) = 5, 300,
extract(“MM”, {Deal - Won time}) = 6, 300,
extract(“MM”, {Deal - Won time}) = 7, 300,
extract(“MM”, {Deal - Won time}) = 8, 400,
extract(“MM”, {Deal - Won time}) = 9, 400,
extract(“MM”, {Deal - Won time}) = 10, 400,
extract(“MM”, {Deal - Won time}) = 11, 300,
extract(“MM”, {Deal - Won time}) = 12, 200,
0)

Field name: Target Sales Weekly (CY23)

{Target Sales Monthly (CY23)}/dateDiff({Deal - Won time}, addDateTime(1,‘MM’, {Deal - Won time}), ‘DD’)*7

[duncan]

Hello @Junny !

I’m glad that helped! My opinion for now is this is probably the best way. My only recommendation is considering adding this to your dataset in Quicksight prior to adding it to the analysis or, if possible for your dataset, try adding the calculation in SQL. This would just be to help with analysis/dashboard load times.

[Junny]

Hi Duncan,
Many thanks for your extra tip, nice to know.

Have a great weekend!
JY