How to get YTD of previous year?

[#1]

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) )"

[hic]

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

[#3]

Thank you so much!

[#4]

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%

[joao_sp_campina]

Hi Mafaz,

See attached an example of a similar situation.

João Carlos Ferreira.

[#6]

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

[#7 · Accepted solution]

find the answer below

If(Avg([Plan GRN Hit Rate_STORES])>Avg([Actual GRN Hit Rate_STORES]), RGB(255,99,71),

If(Avg([Plan GRN Hit Rate_STORES])<=Avg([Actual GRN Hit Rate_STORES]), RGB(50,205,50), White()))