10 Replies Latest reply: Jul 17, 2009 8:27 AM by jiir 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:

       


      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!

       

       

       

        • Set Analysis - Date filters
          Stephen Redmond

          Hi Jeanne,

          Do you have a Calendar table? I.e. a table with one row per day, containing all the Year, Month, Day, Week, etc. values for that date?

          If so, it would be a trivial matter to add a new row:

           


          ...
          1 as DayCounter


          Then, either Sum(DayCounter) or Sum({ < Year = {$(=Max(Year))} > DayCounter) will give you a count of all selected days or all selected days in the latest year.

           

          Stephen

           

            • Set Analysis - Date filters

              Hi Stephen

              Thanks for your suggestion. I have a DayCounter called YTD (basically 0 or 1). The problem I have is that the application only pulls the dates associated the posting dates which means that it only counts the DayCounters with these dates and not with the whole calendar.

              I worked out a fix yesterday which I must say is not elegant but allows me to see the date range the user selects. I took the MinString for the field called Month and the MaxString for the field and calculated the number of days the user selected. This would give me a "static" date range with which to do the budget calculations.

              In this post, I would also like to find out from you or any one else in the forum how to initialize and use variables in set analysis expressions in charts. How do I write code like that in a chart ? Is it like this eg:

               


              Let vSelected= GetFieldSelections(Month)
              SUM( { 1 <Month={$(vSelected)} } Amount)


              I always get an error - is it because I am missing a ; or is it because QlikView does not allow intialization of variables in a Chart expression?

               

            • Set Analysis - Date filters
              jiir

              Hello,

              I have a bit similar problem. I would like to make a single chart comparison between budget and actual sales per months.

              The problem in here is that I've dates from two calendars, so if I choose the month dimension from budget calendar, it doesn't affect to the sum of actual sales and vice versa.

              And i can't link the budget + actual calendars to master calendar and choose the month from there, because I also need the comparison by ledgers ( so I need to keep the link between budget and actual ledger transactions).

              My tables are:

               

              -Ledger info for all ledgers (Budget per ledger and Actual edger transactios are linked to this)
              -Budget per ledger (Budget calendar is linked to this)
              -Budget calendar
              -Actual ledger transactions (Ledger transaction calendars is linked to this)
              -Ledger transaction calendar


               

              Thank you if you can think any kind of solution for me, haven't been using qlikview a lot yet..

                • Set Analysis - Date filters

                  Hi Jiir

                  My application also called for comparing budget with actual figures. I used this solution:

                  1. For each ledger transaction, I specified whether the transaction was "budget" or "actual"

                  2.. Linked transactions posting dates to the master calendar (both budget and actual)

                  3. When it was time to make the chart, I made a filter which took only budget or only actual eg. SUM( IF(BudgetName='Budget', LedgerAmount)).

                  I hope that helps. This solution calls for only one master calendar.