# Revise the visual table to add the periodic target number

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

Hello @Junny , welcome to the Quicksight community!

have you tried the solution provided here?:

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,

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.

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

1 Like

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.

1 Like

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

Have a great weekend!
JY