Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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)
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.
Hi Athanasios,
can you post the sample qvw?
Hello,
I uploaded a qvw with some sample data. I have also added an object with the expressions.
Hi,
Can you check the attachment?
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))
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 .
Hello,
I tried your idea but it didn't work.