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?
