LYTD NOT WORKING WITH AGGR?

[hamzabob1]

Dear All

I have an expression as below which works correctly.

Correct exp:

=num((sum(AGGR(SUM({<BOOKING_TYPE={'G','F'},REGION_NAME={‘A’,’B’},DEPARTURE_YEAR = {$(vCYearD)}>}DISTINCT NO_ADULT),Sales))),'#,##0') 

I want to calculate the Last year YTD for above expression as below for which I am not getting the correct output.

=num((sum({$<DEPARTURE_YEAR=>}AGGR(SUM({<BOOKING_TYPE={'G','F'},REGION_NAME={‘A’,’B’},DEPARTURE_YEAR={'$(vLYearD)'},DEPARTURE_DATE={">=$(vLStart)<=$(vLYEnd)"}>}DISTINCT NO_ADULT),Sales))),'#,##0') 

Definition of Variables which are used for above expression are  as below:

vCYearD  = Year(MaxString(DEPARTURE_DATE))

vLYearD = Year(MaxString(DEPARTURE_DATE))-1

vLStart =   Date(YearStart(Max(DEPARTURE_DATE),-1)) 

vLYEnd  = Date(MonthEnd(Max(DEPARTURE_DATE),-12)) 

Kindly help if there is any modification required in above expression or if there is any other way around the solve this.

Thanks & Regards

Hamza Bobre

[simenkg]

Try substituting $(vLstart) with $(=vLstart). Do the same for the other ones as well.

=num((sum({$<DEPARTURE_YEAR=>}AGGR(SUM({<BOOKING_TYPE={'G','F'},REGION_NAME={‘A’,’B’},DEPARTURE_YEAR={'$(=vLYearD)'},DEPARTURE_DATE={">=$(=vLStart)<=$(=vLYEnd)"}>}DISTINCTNO_ADULT),Sales))),'#,##0')

[hamzabob1]

Dear Simen,

we tried putting = in our expression but its not working...

is there any other way.

Thanks & Regards,

Hamza

[Anil_Babu_Samineni]

Hi, Try like below, I've changes MaxString to Max

LET vLYearD = Year(Max(DEPARTURE_DATE))-1

and then Use as he suggested

=num((sum({$<DEPARTURE_YEAR=>}AGGR(SUM({<BOOKING_TYPE={'G','F'},REGION_NAME={‘A’,’B’},DEPARTURE_YEAR={'$(=vLYearD)'},DEPARTURE_DATE={">=$(=vLStart)<=$(=vLYEnd)"}>}DISTINCTNO_ADULT),Sales))),'#,##0')

[parimikittu]

Hi,

Please try with

num((sum({1<DEPARTURE_YEAR=>}AGGR(SUM({<BOOKING_TYPE={'G','F'},REGION_NAME={‘A’,’B’},DEPARTURE_YEAR={'$(vLYearD)'},DEPARTURE_DATE={">=$(vLStart)<=$(vLYEnd)"}>}DISTINCTNO_ADULT),Sales))),'#,##0')

please replace $ with 1 for Sum.