Set Analysis - Date filters

Wed, 01 Jul 2009 15:19:49 GMT

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:

<blockquote><pre>
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!

topic Set Analysis - Date filters in QlikView

Set Analysis - Date filters