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

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

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

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

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.`
• ###### Set analysis to show Sales between a Starting and a Ending date

I don't know if this is the problem or not, but all your dates need to be in the same format in the set analysis expression. So if, for instance, SalesDate is in format 'YY/MM/DD', but StartDay is in format 'DD/MM/YY', it wouldn't work as is. You would instead need to do something like this:

sum({\$<SalesDate = {">=\$(=date(StartDay,'YY/MM/DD')) <=\$(=date(EndDay,'YY/MM/DD'))"}>} Sales)

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

Thanks John. I need to pay more attention to have the same type of formatting. The expression given by Mike Garcia works very well.

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

Hi John,

I am trying actually the same but want do use the distance between two fields "DateofEntry" and "Abgeschlossen" for the calculation defined by two variables.

I was trying sth like:

= sum({\$<"DateofEntry-Abgeschlossen"={">= \$(AktDelayMin) <= \$(AktDelayMax)"}>} Totals)

but this doesn't work. I have no idea what correct syntax to use.

Any suggestions?

Thanx,

Felix

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

Hi Felix

I don't think you can use a calculation as you do on the left side of the set expression.

If it's possible for you to calculate DateofEntry-Abgeschlossen to a new field AktDelay in your loadscript, you could then use:

=sum({\$<AktDelay={">=\$(AktDelayMin) <=\$(AktDelayMax)"}>} Totals)

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

in my expression i have start date, end date and flag = 1 condition: how do i do it through set analysis..?

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

@cherub_imp:

Do what?

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

@ Goran : i got it..anyways thnks.

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

Hi Goran,

Thanx for your reply, I'll try it the way you described with the script.

But isn't there no way at all in calculating intervals in a set expression?

Anyone?

Thanx,

Felix

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

`Felix Hummel wrote:But isn't there no way at all in calculating intervals in a set expression?`

Of course you can do it. This:

A-B >= C

Is mathematically equivalent to this:

A >= C+B

So you should be able to do something like this, though I suspect I have syntax problems:

sum({<DateofEntry={">=\$(=date(AktDelayMin+Abgeschlossen)) <=\$(date(AktDelayMax+Abgeschlossen))"}>} Totals)

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

I was able to use the above expression to solve a similar problem. However, the numbers change from when I have everything selected to just a single selection and are only accurate with a single selection. Any insight as to what could be causing this? I have also isolated this issue to the greater than sign as the numbers do not change upon selection/de-selection when I change the greater than sign to an equal sign.

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

[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 )

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

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

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

[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

[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

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

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

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

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

`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

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

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

[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.

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