5 Replies Latest reply: Aug 11, 2010 5:05 AM by simonhughes RSS

    Another Date Related Set Analysis Question...

      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)

       

        • Another Date Related Set Analysis Question...
          Jonathan Dienst

          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

            • Another Date Related Set Analysis Question...

               

              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.



               

                • Another Date Related Set Analysis Question...
                  Jonathan Dienst

                  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

                  • Another Date Related Set Analysis Question...
                    John Witherspoon

                     


                    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?