Set Expression YTD not working

[imrasyed]

Hi Experts,

I have an expression below for which iam not able to get the YTD correctly.

Can you please let me know where am I going wrong.

Sum(Aggr(Sum({$<[DePuy Fiscal Year/Month ]={">=$(=Date(Yearstart(Date(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),'YYYYMM')),'YYYYMM'))<=$(=Date(Addmonths(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),-1),'YYYYMM'))"},

[Customer Salesman Code]={'C'},DIVISION_ID ={1,3},[Domestic International Code]={'D'}, [JIT Flag]= {'N'},

[Cust.CustomerNo]-={'7*'},[Territory Number (Time of Sale)]={[80,81,88,93]}>+<[DePuy Fiscal Year/Month ]={">=$(=Date(Yearstart(Date(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),'YYYYMM')),'YYYYMM'))<=$(=Date(Addmonths(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),-1),'YYYYMM'))"},

[Customer Salesman Code]={'C'},DIVISION_ID ={1,3},[Domestic International Code]={'D'}, [JIT Flag]= {'N'},

[Cust.CustomerNo]-={'7*'},[Territory Number (Time of Sale)]={[>=100 and <=299]}>}[Extended Invoice Price]),[Product Number])

/

Aggr(Sum({$<[DePuy Fiscal Year/Month ]={">=$(=Date(Yearstart(Date(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),'YYYYMM')),'YYYYMM'))<=$(=Date(Addmonths(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),-1),'YYYYMM'))"},

[Customer Salesman Code]={'C'},DIVISION_ID ={1,3},[Domestic International Code]={'D'}, [JIT Flag]= {'N'},

[Cust.CustomerNo]-={'7*'},[Territory Number (Time of Sale)]={[80,81,88,93]}>+<[DePuy Fiscal Year/Month ]={">=$(=Date(Yearstart(Date(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),'YYYYMM')),'YYYYMM'))<=$(=Date(Addmonths(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),-1),'YYYYMM'))"},

[Customer Salesman Code]={'C'},DIVISION_ID ={1,3},[Domestic International Code]={'D'}, [JIT Flag]= {'N'},

[Cust.CustomerNo]-={'7*'},[Territory Number (Time of Sale)]={[>=100 and <=299]}>}[Unit Sales]),[Product Number]))

To make it simpler it is

Sum(aggr(Sum({<Date=YTD>}Extended Invoice price),product)

/

Aggr(Sum({<Date=YTD>}Unit Sales),Product))

[MK9885]

Maybe

Sum(aggr(Sum({<Month = {"<=$(=num(Month))"} >}Extended Invoice price),product)

/

Aggr(Sum({<Month = {"<=$(=num(Month))"} >}Unit Sales),Product))

[imrasyed]

Thanks Shahbaz but if you see my above Expression thats what I have written which is not giving me the correct result

[MK9885]

Sum(aggr(Sum({<Month = {"<=$(=num(Month))"} >}Extended Invoice price),product)

/

Aggr(Sum({<Month = {"<=$(=num(Month))"} >}Unit Sales),Product))

Month = {"<=$(=num(Month))"} here is your YTD expression.

Not sure if you mean aggr is not working or the YTD is not?

[imrasyed]

Yup shahbaz

My YTD Expression is working with other set analysis expression having no aggregation on them.

So its not the issue with my YTD expression.Please check the Expression below

Sum(Aggr(Sum({$<[DePuy Fiscal Year/Month ]={">=$(=Date(Yearstart(Date(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),'YYYYMM')),'YYYYMM'))<=$(=Date(Addmonths(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),-1),'YYYYMM'))"},

[Customer Salesman Code]={'C'},DIVISION_ID ={1,3},[Domestic International Code]={'D'}, [JIT Flag]= {'N'},

[Cust.CustomerNo]-={'7*'},[Territory Number (Time of Sale)]={[80,81,88,93]}>+<[DePuy Fiscal Year/Month ]={">=$(=Date(Yearstart(Date(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),'YYYYMM')),'YYYYMM'))<=$(=Date(Addmonths(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),-1),'YYYYMM'))"},

[Customer Salesman Code]={'C'},DIVISION_ID ={1,3},[Domestic International Code]={'D'}, [JIT Flag]= {'N'},

[Cust.CustomerNo]-={'7*'},[Territory Number (Time of Sale)]={[>=100 and <=299]}>}[Extended Invoice Price]),[Product Number])

/

Aggr(Sum({$<[DePuy Fiscal Year/Month ]={">=$(=Date(Yearstart(Date(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),'YYYYMM')),'YYYYMM'))<=$(=Date(Addmonths(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),-1),'YYYYMM'))"},

[Customer Salesman Code]={'C'},DIVISION_ID ={1,3},[Domestic International Code]={'D'}, [JIT Flag]= {'N'},

[Cust.CustomerNo]-={'7*'},[Territory Number (Time of Sale)]={[80,81,88,93]}>+<[DePuy Fiscal Year/Month ]={">=$(=Date(Yearstart(Date(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),'YYYYMM')),'YYYYMM'))<=$(=Date(Addmonths(addyears(date(date#(max([DePuy Fiscal Year/Month ]),'YYYYMM'),'YYYYMM'),0),-1),'YYYYMM'))"},

[Customer Salesman Code]={'C'},DIVISION_ID ={1,3},[Domestic International Code]={'D'}, [JIT Flag]= {'N'},

[Cust.CustomerNo]-={'7*'},[Territory Number (Time of Sale)]={[>=100 and <=299]}>}[Unit Sales]),[Product Number]))

[MK9885]

Its quite a big expression...

stalwar1‌ is an expert is Aggr & set..

Hopefully he can help you with this.

Thanks.

[sunny_talwar · Accepted solution]

May be try this:

Sum({<Date=YTD>} Aggr(Sum({<Date=YTD>}Extended Invoice price)/Sum({<Date=YTD>}Unit Sales), product))

two changes:

1) Used the inner set analysis to the outer set analysis

2) Instead of using two Aggr(), I used just one now since they are both aggregating over just a single dimension product

[imrasyed]

Thanks a Lot Sunny.

Its working like expected.

Thanks again.

[imrasyed]

Thanks Shahbaz for helping me.