6 Replies Latest reply: Jul 31, 2014 2:30 AM by Mafaz Jaufer

# How to get YTD of previous year?

Hi,

I tried bellow query but its not working, find the image in the attachment

"rangesum( above( sum( {\$<Month>}  [Actual SPCD hit rate_CUTTING]),0,12) )"

• ###### Re: How to get YTD of previous year?

Your Set Analysis expression is incorrect. You cannot have {\$<Month>} as Set expression. It needs an equal sign also - but also with an equal sign, I don't understand what it would mean.

You could instead use something like

{\$ <Month={"<=\$(=Month(Max(Date)))"}>}

together with Year as dimension.

But I would instead create a YTD flag in my master calendar in the script:

If( Date - YearStart(Date) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD

This flag can then be used in a chart expression:

Sum( {\$<IsInYTD={1}>} Amount )

I can use any dimension; e.g. Year, Month, Product and the expression will still work. And I can compare YTD between years this way.

HIC

• ###### Re: How to get YTD of previous year?

Thank you so much!

• ###### Re: How to get YTD of previous year?

Hi,

I got a column like below where I want to color If Actual is more than plan green or if plan more than actual red

I used below query in "actual's" background color, it worked but if you notices below table there are empty column and "N/A" in actual column where I want make both white and if it's "N/A" I want to display that on my straight table. please help me on this... how do I do that?

• if(Avg([Plan])> Avg([Actual),RGB(255,99,71),RGB(50,205,50))

 Plan Actual 80% 45% 50% 50% 60% N/A 100% 100% 0% 0% 100% 100% 90% 90% 100% N/A 100% 70%
• ###### Re: Re: How to get YTD of previous year?

Hi Mafaz,

See attached an example of a similar situation.

João Carlos Ferreira.

• ###### Re: How to get YTD of previous year?

Hi Ferreira,

Thanks for try but you have color it blue but that's not what I wanted what I want is;

I want three colors such as;

• If Plan is more than Actual = RED or If Actual is more than Plan = GREEN

and if the cell has the letter "NA" it should read the letter "NA" and the cell should be = WHITE