topic Re: Year to Date - comparing set (dimensionless expressions?) in QlikView

Year to Date - comparing set (dimensionless expressions?)

ronnywaage — Mon, 31 Mar 2014 21:52:06 GMT

-Hi

I have a question regarding YTD calculations. I have spent some time now on this forum but I haven't found exactly what I am looking for. (Maybe it is not a YTD question after all, but it is a YTD problem for me).

We have a table like this (simplified):

VALUE_DATE

VALUE

What I want to acheive is something like SUM({$}VALUE) - SUM({$<VALUE_DATE={"31.12.2013"}>}VALUE).

The problem is to create an expression showing this (with VALUE_DATE as dimension).

I can create an expression for the first leg of the expression. I can also do the same for the second leg, but then I get only a single point which is as expected.

We have solved it by adding an addition column to the table in the load script (EOY_VALUE) which is the value from 31.12.2013 for the matching dimensions, but is there anything I can do in the graph expression to acheive this so we don't have to set up a join in the load script?

Regards,

Ronny

Re: Year to Date - comparing set (dimensionless expressions?)

Mon, 31 Mar 2014 23:48:31 GMT

Hi Ronny,

I don't know if I understood correctly, If not just let me know.

I think the answer you need is something like this:

(

VALUE_DATE<>"31.12.2013",

SUM({<VALUE_DATE-={"31.12.2013"}>}VALUE)

)

Re: Year to Date - comparing set (dimensionless expressions?)

ronnywaage — Tue, 01 Apr 2014 10:28:09 GMT

Thanks you for your answer, and sorry for a bad formulated case from my
side.

What I want to achieve is that I want to subtract the values from the
31.12.2013 from all other days.

Let’s say that the value 31^st of Dec is 5. Then I want to
subtract 5 from all other days.

So for the 1^st of Jan I
want to see the sum of the 1^st of Jan – 5 (the sum of the 31^st
Dec), and the same for Jan 2^nd where I also want to see sum of 2^nd
of Jan – 5.

Regards,

Ronny

Re: Year to Date - comparing set (dimensionless expressions?)

Tue, 01 Apr 2014 13:05:51 GMT

Oh I see Roony,

To do that you need to declare a variable with the expression you are going to substract.

#december31Value

= Sum({<VALUE_DATE={"31.12.2013"}>}VALUE)

// Do not forget to put the "equal symbol" before the expression

then you need to put this variable into your expression calculation:

Sum(VALUE) - #december31Value

This will work for your exact example.

Let me know if that works

Re: Year to Date - comparing set (dimensionless expressions?)

ronnywaage — Tue, 01 Apr 2014 16:28:22 GMT

Thank you very much. I was really an "eye-opener".

Problem solved, working perfect.

Re: Year to Date - comparing set (dimensionless expressions?)

Tue, 01 Apr 2014 19:27:55 GMT

Glad to hear it worked solving your problem.