topic Set Analysis between dates using another fiel in QlikView

Set Analysis between dates using another fiel

Wed, 23 Sep 2009 19:37:24 GMT

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

sum(

{

[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

sum(

{

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

}

Quantity)

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?

Thanks

Matt

Set Analysis between dates using another fiel

Wed, 23 Sep 2009 20:00:29 GMT

Hi Matt,

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

$(=

see your file for more information.

Good luck!

Rainer

Set Analysis between dates using another fiel

Wed, 14 Oct 2009 21:02:02 GMT

Why? This expression dont work.

sum

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

Set Analysis between dates using another fiel

Fri, 23 Oct 2009 18:26:33 GMT

It does in the attached example, yours is the wrong way around?

Set Analysis between dates using another fiel

johnw — Sat, 24 Oct 2009 06:09:51 GMT

Gah! I didn't notice this post was so very very old. And it doesn't look like I can delete posts any more.

Anyway, a trick for debugging set analysis expressions is to make it the first expression in your chart, and to NOT give it a label, or to give the chart itself a title. At that point, your set analysis expression will show as the chart title, and how QlikView is INTERPRETING it will show as the column heading. So in your case, the column heading SHOULD have dates in it, but instead still has LaunchDate. That then tells you that QlikView isn't reading what you wrote like you want it to.

Set Analysis between dates using another fiel

mongolu — Sun, 25 Oct 2009 20:30:12 GMT

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

johnw — Tue, 27 Oct 2009 04:57:18 GMT

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

mongolu — Tue, 27 Oct 2009 06:35:19 GMT

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

Tue, 27 Oct 2009 19:44:47 GMT

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

Set Analysis between dates using another fiel

johnw — Tue, 27 Oct 2009 22:21:21 GMT

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

Fri, 30 Oct 2009 01:58:35 GMT

Well, glad I have managed to provoke some conversation . 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

Set Analysis between dates using another fiel

Mon, 12 Jul 2010 08:04:16 GMT

Hi,

Have you find a solution at the problem ?

Thank you

Christian