11 Replies Latest reply: Jul 12, 2010 4:04 AM by Marco Orso RSS

    Set Analysis between dates using another fiel




      I am trying to calculate the impact of a new product launch. A new product is only new for so long so need to use set analysis to restrict the date range.

      Alongside each sales line I have the launch date for the product.


      so in theory the set analysis should look something like this






      [ShipDate]={">=LaunchDate <([LaunchDate]+35)"}



      Quantity) however if I use this I get no results if I discreetly enter the dates I get the correct answer as below







      [ShipDate]={">=LaunchDate <([LaunchDate]+35)"}






      I have attached an example - am I just doing something wrong with the syntax for using the launchdate or am I trying to do something that is not possible?





        • Set Analysis between dates using another fiel

          Hi Matt,

          what you need is to define what we call an AdHoc Variable


          see your file for more information.

          Good luck!


          • Set Analysis between dates using another fiel
            Mihai Petcu

            I've formatted your second condition in the Set.
            it looks like this and it works:
            SUM( { $ < ShipDate = {">=LaunchDate <$(=DATE(LaunchDate+35,'DD/MM/YYYY'))"} > } Quantity)

            Why? Because i saw in a ListBox the result of LaunchDate+35. It was 39858. Numeric.
            And since the number formatting in the document for both dates was DD/MM/YYYY, i thought that should resolve the problem.
            (practicaly, he compares 10/01/2009 with 39858).

            By the way. In Set Analysis i think the implicit conversion don't happen.
            For example. Format the quantity like this:
            - Number Format Settings = Integer,
            - Format Pattern = 0.00
            and make a chart with this formula:
            SUM( { $ < Quantity = {10} > } Quantity )
            and another one with this formula:
            SUM( { $ < Quantity = {10.00} > } Quantity )
            and see which works.

            And bare in mind that you can use an If function as well.
            SUM( IF(ShipDate>=LaunchDate AND ShipDate<LaunchDate+35, Quantity) )
            (in if function, it takes place an implicit conversion, i think).

              • Set Analysis between dates using another fiel
                John Witherspoon

                I think you're right that in set analysis, the implicit conversion doesn't happen. If you're making a set for a field, you appear to have to format the values in the set so that they match the format of the field. If you don't, they won't match. And as you say, that is different than an IF statement, where the formats don't have to be the same as long as they have the same underlying value.

                  • Set Analysis between dates using another fiel
                    Mihai Petcu

                    Well John. Coming from you (a great person for the community) means a lot.

                    Thanks for your words.


                    So... this is an undesirable headache.
                    (in the manual at page 813, says that a datatype has to representations:
                    - one text,
                    - one numeric (only when the data can be interpreted as a valid number)

                    Perhaps IF uses numeric representation and Set Analysis uses text representation.

                    And from Rob's Wunderlich post (http://qlikviewnotes.blogspot.com/2008/05/memory-sizes-for-data-types.html) regarding memory sizes for data types, i presume that memory utilisation for a Set Analysis using text representation is bigger than one using numeric representation.

                    - numeric : 1-10 digits, 4 bytes

                    - string : "1" uses 11 bytes


                    Should we ask QlikTech about that?


                      • Set Analysis between dates using another fiel

                        i tested the lauchtest.qvw and doesnt work, i'm using the 9.00.7119.4

                        • Set Analysis between dates using another fiel
                          John Witherspoon

                          My understanding was that QlikView is using IEEE double-precision binary floating point for numbers. That's 64 bits, or 8 bytes. If Rob is seeing 4 bytes or 13 bytes depending on the number of digits, that's very curious, and I should probably look into it. For one thing, 32 bits of storage simply isn't enough to represent all possible 10-digit numbers.

                          I believe you're right about IF using numeric representation of numbers, and set analysis using a text representation. I don't have any proof, but it is very consistent with what I've seen, so that's been my working assumption for quite some time now.

                          I highly doubt that this causes a memory or performance problem in most cases due to the way that QlikView seems to work. Say we have a million rows with 100 distinct values for some numeric field. Then we write a set analysis expression like sum({<MyNumber={5,20,100}>} SomeOtherNumber). The only wasted bytes are for the values 5, 20 and 100, which I suspect are stored as text instead of as numbers. That's trivial. When it comes time to execute this expression, set analysis essentially selects those values. To select those values, it would ideally convert them to numbers and then select the matching numbers from the list of 100. Instead, it appears to work the opposite direction, taking the list of 100 numbers, converting to text, and then selecting the matches. But it is a trivial operation to convert 100 numbers to text, so it won't cause any performance problems either. The only time I could see this being a problem is if you use set analysis to select values of a unique ID with millions of values. I suspect all of the conversion to text might then be a big and unnecessary performance headache. But all of these are just guesses on my part.

                            • Set Analysis between dates using another fiel

                              Well, glad I have managed to provoke some conversation Indifferent. I have got this working perfectly but have now added a second complication;


                              I actually want to see this for multiple products with different launch dates, ultimately trying to get a moving average over a 12 month period where a sale is included if it is within 12 months of the launch date

                              Such a scenario is


                              Product 1 is launched 01/02/2008 so it sales would be included in all months until 31/01/2009

                              Product 2 is launched 01/04/2008 so its sales would be included in all months until 31/03/2009


                              if we report on January 2009 both products sales should be included and if we report February 2009 only product 2 would be included even if there were sales for February 2009.


                              I have attached an example using different dates as I'm not sure it is as clear as the above. But what happens basically is that the expression can't work out what the date should be. Am thinking I should be combing with an if function but not sure this would be any better? any help much appreciated