Another Date Related Set Analysis Question...

[#1]

Hello,

Can anyone help with the following date related set analysis problem please?

Example data:

Order Date Ref
2007-01-01 r123
2008-01-30 r124
2008-02-01 r125
2008-02-03 r126
2008-02-04 r222
2009-02-05 r345

In the load script I have a calculation of:
Year([Order Date]) as OrderDateYear

What I require is to count the number of refs for each year, irrespective of any current selections.

This works as expected:
=Count({1<OrderDateYear={2008}>}[Ref])

This does not work as expected (it returns the count of Refs for all loaded data (irrespective of year)):
=Count({1<Year([Order Date])={2008}>}[Ref])

This also doesn't work (it returns the same as above):
=Count({1<Year(MakeDate(Left([Order Date],4),Mid([Order Date],6,2),Right([Order Date],2)))={2008}>}[Ref])

Is it the case that an expression applied to the left hand operand isn't supported in set analysis?
Any guidance would be great thanks?

(I'm using QlikView 64-bit Personal Edition 9.00.7502.0409)

[jonathandienst]

I do not understand what you are asking. The first set expression works and answers your own question. If not, perhaps you could be more specific: for example, do you want a chart/table with this data?

As to your last expression, the LHS can only be a field, not an expression.

Jonathan

[#3]

Yes, the only way I can get this to work is by altering the load script to include the Year() function.

However I require many such expressions and am trying to avoid writing additional Year(), Month(), Day()... expressions in the load script for each date concerned, so would prefer to use the format of:

=Count({1<Year([Order Date])={2008}>}[Ref])

But, alas, I cannot get it to work. If I understand correctly, this is due to the LHS being an expression and not just a stand-alone field?

And yes, I would like this to be in a chart, e.g. straight table.

[jonathandienst · Accepted solution]

Well you might not need to create a Year field if the only place you want use it is in a chart. There is nothing stopping you from using Year([Order Date]) as a calculated dimension and then using Count([Ref]) or Count({1}[Ref]) as the expression (the second one will override any selections but will respect the chart dimensions).

I suspect that you will get better performance if you do create a Year field, but that may or may not be important in this case.

Hope that helps!

Jonathan

[johnw]

Simon H wrote:
=Count({1<Year([Order Date])={2008}>}[Ref])
But, alas, I cannot get it to work. If I understand correctly, this is due to the LHS being an expression and not just a stand-alone field?

Correct. The problem is that the left hand side must be a field, not an expression. Now, if it's acceptable to hardcode the years like you've done, you can do it like this (hopefully I didn't make a syntax error, but this stuff gets ugly fast):

count(1<[Order Date]={">=$(=date(date#(20080101,'YYYYMMDD'))) <=$(=date(date#(20081231,'YYYYMMDD')))"}>} [Ref])

But if you don't want to hardcode years, and you want the years as a dimension, then you'd need to use a calculated dimension like Jonathan said. It's simpler anyway. The main problem is performance. Performance would be significantly better if you add a Year field.

I would add the Year field. I'm not sure why you're reluctant to add one. Script complexity? Load speed? Memory? Something else?

[#6]

Thank you both for the information - a great help.

I was reluctant to add a Year field (as well as Month and Day etc...) in an attempt to keep the script as simple as possible, but now see good reason to go down that path.

Thanks,

Simon.