Set Analysis between dates using another fiel

[#1]

HI

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

[#2]

Hi Matt,

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

$(=

see your file for more information.

Good luck!

Rainer

[#3]

Why? This expression dont work.

sum

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

[#4]

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

[johnw]

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.

[mongolu]

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

[johnw]

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.

[mongolu]

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?

[#9]

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

[johnw]

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.