Hi everyone
I need some help on date filters through set analysis. Although I have read widely in the forum, I still cannot figure how to solve me problem.
My application contains one table called Chart of Accounts which have general ledger entries and posting dates. Budget entries are posted once a month. I created a calendar and linked the table to the GL posting dates. Posting dates are not consecutive so the table only pulls the dates from the calendar where postings are made.
When I make a chart showing actual vs budget expenses, I am supposed to calculate the budget YTD in this way:
1. Sum all budgets figures for one whole year eg 2009
2. Calculate the budget YTD by (Number of Days in Date Range Selected) / (Total Days in the Year) times the entire budget figure for the year. For eg for Jan to Apr and a budget of 12000, the Budget YTD would be 120/365 times 12000 = 3945.
My problem comes from the associative way QlikView pulls dates. I simply cannot figure how to get the selection called Number of Days in Date Range Selected and to put it in a Set analysis formula.
I have tried GetFieldSelections(Calendar_Month) but how do I convert the values to calculate number of days the user selects and use this number?
My set analysis currently looks like this:
SUM( {1 < [Calendar_Month] = {GetFieldSelections([Calendar_Month] >} Total [Calendar_YTD] }
Where [Calendar_YTD] is a boolean number field in the Calendar (0 or 1 depending on whether the current date is YTD)
Thanks in advance!
