Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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)