YTD BUDGET

[qlikviewaf]

Hi Guys,

i'm struglling a little bit in calculating budget on my dashboard.

I loaded the monthly-year budget from an excel file.

For every budget row there's a date which is always the end-month-date (e.g. budget of august 2017 is loaded with as date 31/08/2017)

I already calculated the Daily budget, the MTD Budget, but now i have to calculate the YTD Budget.

I have to calculate the budget in this way: MTD Budget (Already calculated) + Previous months budget (from the beginning of the year).

How i can calculate the Previous month budget? (E.G. i select as date 5-may-2017, i would like to have sum of budget from 1 jan 2017 till 30 april 2017)

Thank you!

(find attached also my dashboard)

[sunny_talwar]

Not sure what the expected output needs to look like, but may be this?

((Sum({<Month, Quarter=, Day=,Invoice_Date=>} BudgetUSD)/1000)/

$(vWorkingDaysYTD))* $(vWorkingYTD)

[qlikviewaf]

no this is completly wrong, the YTD is less than a single month budget!

[sunny_talwar]

So it should be less than monthly budget? Can you give me a number for one of the regions? May be Belgium?

[qlikviewaf]

let's do it simpler

Which is the set analysis syntax that i need to use to select:

From first day of the year ---> till the last day of the previous month, based on the day-month-year selected?

[sunny_talwar]

Like this

=Sum({<Year, Month, Quarter, Day, Invoice_Date_Num = {"$(='>=' & Num(YearStart(Max(Invoice_Date_Num))) & '<=' & Num(Max(Invoice_Date_Num)))"}>} BudgetUSD)