Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
i have loaded my budget as concatenation of my sales table.
The budget for every month is loaded with the last day of the month
e.g. Budget of may 2017 is 31/05/2017
-----------------
On my dashboard i have to compare the sales with the budget.
Imagine that i select as day of analysis the 10th of May2017 - i don't want to have comparison between the MTD Actual vs TOTAL BUDGET OF THE MONTH.
I would like to recalculate the budget as follow:
BUDGET OF MAY (Sum({<Quarter=, Day=,Invoice_Date=>} BudgetUSD)/1000)
divided by total number of working day for May2017 and multiplied by the number of working days till the day selected
To do this i created on the master calendar a field WorkingDay that basically it's a flag that tells me if the day is working or not.
Can someone please help me on this? I guess i should use the COUNT function?
Thank you!
May be this?
(Sum({<Quarter=, Day=,Invoice_Date=, Month = {'$(=Max(Month))'}>} BudgetUSD)/1000) / (Count({<Month = {'$(=Max(Month))'}>} TOTAL Day) * Count({<Day = {">=$(=Min(Day)) <=$(=Max(Day))"}>}Day))
Or
(Sum({<Quarter=, Day=,Invoice_Date=, Month = {'$(=Max(Month))'}>} BudgetUSD)/1000) / (Count({<Month = {'$(=Max(Month))'}>} TOTAL Day) * Count({<DateField = {">=$(=Min(DateField)) <=$(=Max(DateField))"}>}Day))