Problem with % YTD v PriorYTD

[rustyfishbones]

Hi All,

I have a problem when calculating % difference YTD v Prior YTD

here is what I have in Text Object, it works when the numbers are positive, but not when they are Negative

See below table, in this table I am looking at this year v last year, it shows a % increase of 223% which to me is correct

However, If I select Year 2010 from my list box, I get the follow result

I am expecting to see a variance of -128% like below

Do I use an IF Statement in the Expression at the top of this post? If so, how do I need to correct it? any help would be greatly appreciated as my head is not with it at the moment

Thanks

Al

[er_mohit]

Try this way using min date and max date for both side using variable like this make variable

vStartDate =  =monthstart(Date(Min(Date)))

vEndDate =  = Date(Max(Date))

vPYStartDate = =Addmonths('$(vStartDate)',-12)

vPYEndDate =  =Addmonths('$(vEndDate)',-12)

So in expression side

write

(sum({<Year = {'$(vMaxYear)'},Date = {'>=$(vStartDate)<=$(vEndDate)'}>} line_amount)

-

sum({<Year = {'$(vPriorYear)'},Date = {'>=$(vPYStartDate)<=$(vPYEndDate)'}>} line_amount))

/

sum({<Year = {'$(vPriorYear)'},Date = {'>=$(vPYStartDate)<=$(vPYEndDate)'}>} line_amount) *100

[tresesco]

Could you post your sample app that demonstrates the issue?

[rustyfishbones]

Hi tresesco,

PFA

[tresesco]

In  chart expression, I guess you have to correct the denominator by right column number (2 rather than 1), like:

=NUM((column(1) - column(2)) / column(2),'0.00%')

[rustyfishbones]

Hi,

I tried that already, it will work if the COLUMN(1) Number is always the bigger number, but if the COLUMN(1) is less than COLUMN(2) I run into problems

If you promote the Expression in COLUMN(2) you will see what I mean

[tresesco]

So you want to keep the flexibilty of changing column order. Then use expression directly like:

=(NUM(SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'}>}[line-amount]),'#,##0')-

NUM(SUM({<Year={'$(vPriorYear)'},Date={'<=$(vPriorYearDate)'}>}[line-amount]),'#,##0')

)/

NUM(SUM({<Year={'$(vPriorYear)'},Date={'<=$(vPriorYearDate)'}>}[line-amount]),'#,##0')

Instead of column reference.

[rustyfishbones]

Yes, I tried that also.

I still have the issue where it works when the Max Year is greater then the Prior year, but if the max year is less than the previous year the result returned is incorrect.like below

I expect the % variance to be -128% for the above case, 4 Million drop in 2009 to 1.7 Million in 2010 is more than a decrease of 56%

[tresesco · Accepted solution]

Well, this:

=NUM((SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'}>}[line-amount])-

SUM({<Year={'$(vPriorYear)'},Date={'<=$(vPriorYearDate)'}>}[line-amount])

)/

RangeMin(SUM({<Year={'$(vPriorYear)'},Date={'<=$(vPriorYearDate)'}>}[line-amount]),SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'}>}[line-amount])),'##.##')

[rustyfishbones]

Thanks tresesco