24 Replies Latest reply: Dec 10, 2010 2:59 PM by kcampbell RSS

    Set analysis to show Sales between a Starting and a Ending date

    banciu

      I want to show Sales from a specific period (user can select two variables: start date and end date) using Set analysis. I have managed to do this for one day:

      sum( {$< SalesDate = {'$(StartDay)'} >} Sales)

      How to do for a period?

      sum( {$< SalesDate >= {'$(StartDay)' and SalesDate <= {'$(EndDay)'} >} Sales). This is just an idea of what I need, of course this expression that I wrote doesn't work (and I can't make it work).

      Thank you.

        • Set analysis to show Sales between a Starting and a Ending date
          Miguel �ngel Garc�a

          Hi, Try

          sum({$<SalesDate = {">= $(StartDay) <= $(EndDay)"}>} Sales)


          (Notice the double quotes to indicate a search string)

          Hope this helps.

          Mike.

            • Set analysis to show Sales between a Starting and a Ending date
              banciu

              Thanks for the answer, Mike.

              From what I have read in the reference manual, your formula looks pretty good at first glance.

              The problem is that it is not working for me. Probably this is happening because the logical operators (greater than, less than, etc) are acting differentely on set analysis when dealing with dates and variables (my variables are defined as a predefined number series in input box, but shown as date - in Numbers tab).

              For one date, I am using this fomula:

              sum({$<SalesDate = {'$(StartDay)'}>} Sales)

              and it is working.

              The problem appears when I want to do the same thing, but for an interval.
              • SV:Re: Set analysis to show Sales between a Starting and a Ending date
                Goran Korsgren

                [quote user="Mike García"]

                Hi, Try

                sum({<SalesDate = {">= $(StartDay) <= $(EndDay)"}>} Sales)

                (Notice the double quotes to indicate a search string)

                Hope this helps.

                Mike.

                 

                The above syntax will not give what you would expect if StartDay is after EndDay. (Try it and you will see what I mean Wink)

                The correct way to write this is:

                sum({$<SalesDate = {">= $(StartDay)"} * {"<= $(EndDay)"}>} Sales)
                This will guarantee that you get 0 for sum(Sales) if StartDay is after EndDay

                 

                  • SV:Re: Set analysis to show Sales between a Starting and a Ending date
                    banciu

                    Thanks Goran. Your expression is remarkable. I always assumed that the users will know to choose an EndDate which is greater than the StartDate. I don't have to assume this anymore.

                      • SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                        Goran Korsgren

                        Well, of course you may be able to prohibit the users to select an EndDate smaller than the StartDate,

                        but the syntax I suggested works also for all kind of other sets. You will get the intersection of the two sets.

                         

                        I am not sure really how the syntax suggested by Mike should be interpreted

                        Edit:

                        I experimented a bit and it seems that Mike's syntax works like INTERSECT ( *-operator ) as long as StartDate <= EndDate,

                        but when StartDate is larger than EndDate Mike's syntax works like UNION ( +-operator ) or like XOR ( /-operator ) (I can't decide which because they are the same for two non-overlapping sets)

                          • SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                            Miguel �ngel Garc�a

                            [quote user="Göran K"]I am not sure really how the syntax suggested by Mike should be interpreted

                            Let me try and explain the syntax, so that it can be properly understood and every developer can evaluate both approaches and decide which one to take.

                            The part

                            ">= $(StartDay) <= $(EndDay)"
                            indicates a search string. Just as you would search on a list box containing numbers, for example. The string ">5<10" will give you numbers (if available) ranging from 6 to 9. That's just how it works and it is the same for dates.

                            If your search is something like ">10<5" (Start bgreater than End) it will return NO values, since there is no number that meets both criteria. So, that covers the point of "What if a user selects a StartDate greater than End Date?". It will just return zero, as the other approoach does.

                            And no, it does not work like Union Operator, nor does it work like XOR.

                            I did not take the time to do some testing but I believe that specifying ONE set (with one search string) is more efficient that specifying TWO sets and then use an INTERSECT operator.

                            Anyway, I've always liked the idea of having different solutions for the same problem and welcome new ideas.

                            Mike.

                              • SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                                John Witherspoon

                                [quote user="Mike García"]I did not take the time to do some testing but I believe that specifying ONE set (with one search string) is more efficient that specifying TWO sets and then use an INTERSECT operator.

                                I haven't tested it either, but I'm pretty confident that you are correct based on my hopefully correct understanding of what's going on here. However, I'd also bet that Göran's expression WON'T be slower (or at least not noticeably), because QlikView should be smart enough to simply intersect the two search results for a single field, rather than intersecting two complete sets of data. Basically, I'm looking at three ways of doing this. Yours and Göran's should be fast, and the one I will introduce should be much slower:

                                sum({<SalesDate = {">= $(StartDay) <= $(EndDay)"}>} Sales)

                                This uses a single search expression that tells QlikView to "select" dates between the start and end date. So let's say you "select" three months out of 30 months. QlikView's normal associative logic is borrowed, and you almost immediately have the data you requested, about 10% of what is available.

                                sum({<SalesDate = {">= $(StartDay)"} * {"<= $(EndDay)"}>} Sales)

                                With this expression, QlikView will probably first use the search expression ">=$(StartDay)" to get a list of all SalesDates greater than or equal to the start day. That's a short list, so this is extremely fast. Then it will do the same for EndDay, which should also be extremely fast. Then it should intersect these two short lists. That should be extremely fast. Then it should proceed like it did in the previous expression, "selecting" those fields, borrowing QlikView's normal associative logic, and almost immediately giving you the 10% of data you wanted. It should be only a tiny bit slower if everything works like I suspect.

                                sum({<SalesDate = {">= $(StartDay)"}> * <SalesDate = {"<= $(EndDay)"}>} Sales)

                                THIS is where I suspect you could get into performance trouble, because I think QlikView will intersect two sets of data intead of just two lists of field values. So I suspect this will take over twice as long as the previous two. Let's say you selected the middle three months or so. QlikView "selects" all dates less than the End, or about 60% of your data, and that's data set one. Then QlikView "selects" all dates greater than the Start, or about 60% of your data, and that's data set two. Then QlikView does an intersection operation on the two sets, keeping the 10% of rows that are in both sets. Doing all of that is expensive, at least done "naively" like this, which is what I suspect it does.

                                Now, I could be wrong. For instance, QlikTech could have coded some pre-optimization that recognizes that this is an intersection of two sets for the same Field, and therefore intersect the values for the field FIRST, which should be much faster. And it might be fun to test and see. I just haven't.

                                • SV:Re: SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                                  Goran Korsgren

                                  Hi Mike!

                                  I appreciate the first part of your explanation. It makes it very easy to see what's happening.

                                  But when you say

                                  If your search is something like ">10<5" (Start bgreater than End) it will return NO values, since there is no number that meets both criteria.
                                  that is wrong.

                                   

                                  It is simple to test, do it for yourself if you don't believe me. I created a simple table with values "value": 10, 20, 30 ... and so on.

                                  Then I created a listbox for "value" and did the following search, see figure below for search and result.

                                  As you can see ">90 <70" gives everything below 70 and everything above 90, exactly as a UNION or XOR would do.

                                  ">20 <70" on the other hand gives everything between 20 and 70 (like INTERSECT on >20 and <70):

                                   

                                  Thanks

                                  /Göran

                                  • SV:Re: SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                                    Goran Korsgren

                                    Hi guys

                                    the attached app demonstrates the concepts we are debating in this thread, and proves that what's said here is wrong: [quote user="Mike García"]If your search is something like ">10<5" (Start bgreater than End) it will return NO values, since there is no number that meets both criteria. So, that covers the point of "What if a user selects a StartDate greater than End Date?". It will just return zero, as the other approoach does.

                                    And no, it does not work like Union Operator, nor does it work like XOR.

                                    In fact what ">10 <5" returns is all numbers that meets one of the criteria (a UNION that is)

                                     

                                    Best Regards

                                    /Göran

                                      • SV:Re: SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                                        John Witherspoon

                                        Interesting! I did not know that search strings behaved that way, and it kind of makes sense, so it seems like a feature.

                                        Well, how about this?

                                        sum({<SalesDate={">=$(=rangemin(StartDay,EndDay)) <=$(=rangemax(StartDay,EndDay))"}>} Sales)

                                        I think in many cases, swapping the start and end like that would be preferable to returning nothing at all. In fact, you might want to use it to reverse the sort order. If I ask to start on February 15, 2010 and end on February 1, 2010, maybe I want my dates in reverse order. What I'm probably NOT asking for is for QlikView to return nothing at all (not that it's a big deal, since the user can always reverse their dates manually).

                                          • SV:Re: SV:Re: SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                                            Goran Korsgren

                                             


                                            John Witherspoon wrote:
                                            Interesting! I did not know that search strings behaved that way, and it kind of makes sense, so it seems like a feature.
                                            Well, how about this?
                                            sum({<SalesDate={">=$(=rangemin(StartDay,EndDay)) <=$(=rangemax(StartDay,EndDay))"}>} Sales)
                                            I think in many cases, swapping the start and end like that would be preferable to returning nothing at all. In fact, you might want to use it to reverse the sort order. If I ask to start on February 15, 2010 and end on February 1, 2010, maybe I want my dates in reverse order. What I'm probably NOT asking for is for QlikView to return nothing at all (not that it's a big deal, since the user can always reverse their dates manually). <div></div>


                                            Yes, that's a clever solution, if you are sure that that is what you want to achieve.

                                            In the case where the user explicitly inputs two dates (or other numbers) to indicate an interval Johns suggestion above can be a good solution.

                                             

                                            But I can imagine cases where you have two dates (or other numbers), and where the "Start" date naturally is after the "End" date and you actually wants Qlikview to return 0.

                                             

                                            So all-in-all, what I wanted to point out was that in Qlikview searchstrings like

                                            >=10 <=5
                                            do not return what a lot of people would guess.

                                            But as long as you are aware of that you can always find a solution.

                                            TMT1WTDI

                                            /Göran

                                             

                                          • SV:Re: SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                                            Miguel Angel Baeyens de Arce

                                            Hello Göran,

                                            It happened to me by mistake, and it came to my head that the results returned are what you are looking for, matching both criteria, but in the specified order, (because there are results that match them, so the set modifier is true). Actually, a UNION.

                                            @Mike: Try with Max 15 and Min 3, it will return, in that order, all below 15 and all above 1, so the count increases.

                                            @John: nice solution for this "issue" I haven't thought of. Thanks!

                                            • SV:Re: SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                                              Miguel �ngel Garc�a

                                              Wow! I certainly did not expect those results.

                                              "Never assume the obvious is true."

                                              So if the search is done the "logical" way (the Start lower than the End) then both conditions are evaluated win an AND operator, otherwise it behaves as if using an OR Operator.

                                              I really like this discussions in which I can always acquire new knowledge. Thanks Göran!

                                              Mike.

                                                • SV:Re: SV:Re: SV:Re: SV:Re: Set analysis to show Sales between a Starting and a Ending date
                                                  Goran Korsgren

                                                  [quote user="Mike García"]

                                                  Wow! I certainly did not expect those results.

                                                  "Never assume the obvious is true."

                                                  So if the search is done the "logical" way (the Start lower than the End) then both conditions are evaluated win an AND operator, otherwise it behaves as if using an OR Operator.

                                                  I really like this discussions in which I can always acquire new knowledge. Thanks Göran!

                                                  Mike.

                                                   

                                                  You're welcome Mike. I am glad to be able to contribute, even though I two months ago never had seen a single line of Qlikview code. Smile

                                                   

                                                  I just checked in the official documentation for anything about this behaviour for "Search" but there's nothing. That is a bit strange, since it does not behave "natural".

                                                   

                                                  /Göran