How to create a projected revenue field given the daily average of the month?

I want to calculate projected revenue for the filtered month, given the amount realized so far. Below is a picture of my dataset (with dummy data).
The green fields are from my original data, the ones in orange are the fields calculated in quickisght, and in blue the desired field.

Basically, what I would like to do is the following formula:
(accumuated_revenue / number_day) * last_day_month

However, quickisght does not accept calculated fields with nested functions.

I don’t know the best way to create this field. The idea would be to use it in a KPI or sauge chart

Hello @lamartine . Thanks for sharing your query. This is tricky; but I believe the result you are looking for can be achieved by playing around with the functions. Please see the below snapshot for the tweaked calculation that I have used based on my dataset.

// Accumulated Qty

sumOver({accumulated_qty}, [{Order Date}])

// Divided by operator

/

//Number Day

avgOver(max({number_day}), [{Order Date}]) *

//Last Day

avgOver(max({last_day}), [{Order Date}])

For your case
accumulated_qty will be accumulated_revenue
Order Date will be date
number_day will be number_day
last_day will be last_day_month

image

Can you please try this and let me know if this serves the purpose? Thank you!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

Hi @sagmukhe, thanks for your answer.

The code returns the following error: VISUAL_CALC_REFERENCE_MISSING

Hello @lamartine - I could replicate the calculations that I provided in the visual in similar manner how you wanted. Please see the below snapshot for reference. The only difference is that instead of revenue, I used Quantity. Can you please share your calculations that you are trying out.

image