Calendar: Days Calculation

qlikviewaf — Thu, 29 Jun 2017 15:13:49 GMT

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!

Re: Calendar: Days Calculation

Anil_Babu_Samineni — Thu, 29 Jun 2017 17:17:51 GMT

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))

(Sum({<Quarter=, Day=,Invoice_Date=, Month = {'$(=Max(Month))'}>} BudgetUSD)/1000) / (Count({<Month = {'$(=Max(Month))'}>} TOTAL Day) * Count({<DateField = {">=$(=Min(DateField)) <=$(=Max(DateField))"}>}Day))

topic Calendar: Days Calculation in QlikView

Calendar: Days Calculation

Re: Calendar: Days Calculation