topic PYTD problem expression in QlikView

PYTD problem expression

Thu, 08 Oct 2015 14:23:23 GMT

Hello everyone,

I need some help with an expression.

The following expression calculates YTD sales with some limitations and it works fine

sum(Aggr(If([Transaction Number] = Max({<Year={$(=max(Year))},Date={"<=$(=max(Date))"}, Production= {'New Production'}>} TOTAL <[ Contract Number]> [Transaction Number]), [Sales]), [Contract Number],[ Transaction Number],RIDER_CODE))

When I want to calculate PYTD sales I change the expression

sum(Aggr(If([Transaction Number] = Max({<Year={$(=max(Year)-1)},Date= {"<=$(=addmonths(max(Date),-12))"}, Production= {'New Production'}>} TOTAL <[ Contract Number]> [Transaction Number]), [Sales]), [Contract Number],[ Transaction Number],RIDER_CODE))

If I have no selection both expressions return result. When I select a Year (for example 2015) the YTD sales expression works but the PYTD returns 0 value. I cannot find any logical reason why this is happening. Has anybody any idea ?

Re: PYTD problem expression

settu_periasamy — Fri, 09 Oct 2015 02:57:34 GMT

Hi Athanasios,

Just to confirm, do you have the previous year data? I mean 2014 data (as you mentioned, you selected the year as 2015)

Re: PYTD problem expression

Fri, 09 Oct 2015 06:48:29 GMT

Yes I have data from 2000 up to 2015. When I have no selections the calculations of the expressions are correct. They return sales for 2015 and 2014. But when I make a selection for example 2015 I should have the same results. The issue is that YTD works but PYTD returns 0. This happens for every year I choose.

Re: PYTD problem expression

settu_periasamy — Fri, 09 Oct 2015 09:38:16 GMT

Hi Athanasios,

can you post the sample qvw?

Re: PYTD problem expression

Fri, 09 Oct 2015 10:39:19 GMT

Hello,

I uploaded a qvw with some sample data. I have also added an object with the expressions.

Re: PYTD problem expression

settu_periasamy — Fri, 09 Oct 2015 17:21:23 GMT

Hi,

Can you check the attachment?

Re: PYTD problem expression

pamaxeed — Fri, 09 Oct 2015 17:26:12 GMT

Have you tried to ignore the Year Filter like that:

sum({<Year=>}Aggr(If([Transaction Number] = Max({<Year=, Year={$(=max(Year)-1)},Date= {"<=$(=addmonths(max(Date),-12))"}, Production= {'New Production'}>} TOTAL <[ Contract Number]> [Transaction Number]), [Sales]), [Contract Number],[ Transaction Number],RIDER_CODE))

Re: PYTD problem expression

Mon, 12 Oct 2015 07:17:39 GMT

Hello,

I checked your file. The issue is that the results don’t seem to be right. The results on that file should be YTD 2015 vs PYTD 2014 : 3520.28 vs 0 (because we have data for 2015 up to 7/1/2015 and for 2014 our data start from 8/1/2014) and YTD 2014 vs PYTD 2013 : 242355 vs 406544.3 .

Re: PYTD problem expression

Mon, 12 Oct 2015 07:18:32 GMT

Hello,

I tried your idea but it didn't work.