Week comparison without set analysis

[#1]

Hi,

I have the below expression and it works as expected.

=Sum(Aggr((If(Sum(CS_QTY) >0 and PROD <>'C', Count(DISTINCT MATERIAL),0)), MATERIAL))
 

I would like to add one more column to see the previous yearweek results. i.e., if I select 201506 (yearweek), the first column should display the above results and the second column should display results for 201406. I tried using set analysis, but I am unsuccessful Can you please guide me on this.

Thanks

Sam

[marcus_sommer]

I would try it in this way, with querying the max. Year and Week separately assuming that you such fields have and they are connected with your YearWeek (if not I recommend to do it) and assuming that you don't need a sum-check on CS_QTY and a simple check > 0 is enough.

=Sum(Aggr(

Count({< CS_QTY = {">0"}, PROD -= {'C'}, Year = {"$(=max(Year))"}, Week = {"$(=max(Week))"} >} DISTINCT MATERIAL), MATERIAL)) // current year depending on your selections

=Sum(Aggr(

Count({< CS_QTY = {">0"}, PROD -= {'C'}, Year = {"$(=max(Year)-1)"}, Week = {"$(=max(Week))"} >} DISTINCT MATERIAL), MATERIAL)) // last year

- Marcus

[sunny_talwar]

If time is not your dimension, you might be able to use this:

=Sum(Total <yearweek> {<Year = {"$(=Max(Year))"}>}Aggr((If(Sum(Total <yearweek> {<Year = {"$(=Max(Year))"}>}CS_QTY) >0 and Only(Total <yearweek> {<Year = {"$(=Max(Year))"}>} PROD) <>'C', Count(Total <yearweek> {<Year = {"$(=Max(Year))"}>} DISTINCT MATERIAL),0)), MATERIAL))

If yearweek is your dimension, then you might need to use Above function here