0 Replies Latest reply: Jul 1, 2009 11:19 AM by jeannepetersen RSS

    Set Analysis - Date filters

      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!