1 Reply Latest reply: Aug 2, 2013 10:42 AM by David Ambrozie RSS

    Can I nest one set_expression in another?

    Friedrich Hofmann

      `Hi,

       

      I'm trying to do something rather complex on scripting level and, since I cannot yet figure it out, I would like to try the same thing on GUI-level:

      - I want a diagram with categories (from to) as my x-axis and the count of employees within each category as y-axis.

      - I have a table where, for every day and for every employee present on that day, there is an entry with a pos. or neg. nr. of hrs/min that employee worked (more or less than his due)

      - When I sum all days up for one employee, I get a correct total as of today.

      <=> I want to be able to select any day in the past and get that category-view on that day (so in the background I need the total of every employee on any given day, which means summing up all individual values from that employee's entry date up to that day)

       

      Can I "simply" write, as formula in my diagram, sth like >> count of all employees for who the sum of all individual values from the first day (I have filtered for the entry_date of every employee) up to the selected day is within a certain category <<? => two nested set_expressions

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Can I nest one set_expression in another?
          David Ambrozie

          Hello Friedrich,

               It is possible, but it depends on your data model. It will be something like:

               count({<EmployeeID={"=sum({<Date = {"<=$(vSelectedDate)"}>}Value) >=x <=y "}>}EmployeeID)

           

          From QlikView Help: Set Analysis

           

          Set Modifiers with Advanced Searches

          Advanced searches using wildcards and aggregations can be used to define sets.

          Examples:

          [...]

          sum( {$<Customer = {“=Sum({1<Year = {2007}>} Sales ) > 1000000”}>} Sales )
          returns the sales for current selection, but with a new selection in the “Customer” field: only customers who during 2007 had a total sales of more than 1000000.

           

          Best regards,

          David