topic Re: YTD BUDGET in QlikView

YTD BUDGET

qlikviewaf — Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: YTD BUDGET

sunny_talwar — Thu, 03 Aug 2017 14:17:02 GMT

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

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

$(vWorkingDaysYTD))* $(vWorkingYTD)

Re: YTD BUDGET

qlikviewaf — Thu, 03 Aug 2017 14:20:10 GMT

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

Re: YTD BUDGET

sunny_talwar — Thu, 03 Aug 2017 14:27:11 GMT

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

Re: YTD BUDGET

qlikviewaf — Thu, 03 Aug 2017 14:49:27 GMT

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?

Re: YTD BUDGET

sunny_talwar — Thu, 03 Aug 2017 19:18:37 GMT

Like this

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