topic Re: Problem with % YTD v PriorYTD in QlikView

Problem with % YTD v PriorYTD

rustyfishbones — Tue, 03 Dec 2013 11:06:09 GMT

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

Re: Problem with % YTD v PriorYTD

er_mohit — Tue, 03 Dec 2013 11:33:50 GMT

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

Re: Problem with % YTD v PriorYTD

tresB — Tue, 03 Dec 2013 11:43:30 GMT

Could you post your sample app that demonstrates the issue?

Re: Re: Problem with % YTD v PriorYTD

rustyfishbones — Tue, 03 Dec 2013 11:50:05 GMT

Hi tresesco,

PFA

Re: Problem with % YTD v PriorYTD

tresB — Tue, 03 Dec 2013 12:23:29 GMT

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%')

Re: Problem with % YTD v PriorYTD

rustyfishbones — Tue, 03 Dec 2013 12:27:25 GMT

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

Re: Problem with % YTD v PriorYTD

tresB — Tue, 03 Dec 2013 12:31:50 GMT

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

Instead of column reference.

Re: Problem with % YTD v PriorYTD

rustyfishbones — Tue, 03 Dec 2013 12:43:36 GMT

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%

Re: Problem with % YTD v PriorYTD

tresB — Tue, 03 Dec 2013 12:55:28 GMT

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])),'##.##')

Re: Problem with % YTD v PriorYTD

rustyfishbones — Tue, 03 Dec 2013 13:39:21 GMT

Thanks tresesco