Year to Date - comparing set (dimensionless expressions?)

[ronnywaage]

-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

<many other dimensions>

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

[#2]

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:

if

(

     VALUE_DATE<>"31.12.2013",

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

)

[ronnywaage]

Hi

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

[#4 · Accepted solution]

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

[ronnywaage]

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

Problem solved, working perfect.

R

[#6]

Glad to hear it worked solving your problem.