Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

24 Replies
gandalfgray
Specialist II
Specialist II

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

gandalfgray
Specialist II
Specialist II

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

johnw
Champion III
Champion III

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

Miguel_Angel_Baeyens

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!

mike_garcia
Luminary Alumni
Luminary Alumni

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.

Miguel García
Qlik Expert, Author and Trainer
gandalfgray
Specialist II
Specialist II

[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

gandalfgray
Specialist II
Specialist II


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

Not applicable
Author

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

gandalfgray
Specialist II
Specialist II

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)

Not applicable
Author

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