Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewaf
Creator
Creator

YTD BUDGET

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)

budget.PNG

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)

5 Replies
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
Creator
Creator
Author

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
Creator
Creator
Author

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)