Help needed on set analysis

[#1]

Hi All,

Hope everyone is doing great.

I need small help in indirect set analysis.

I am calculating current year Sales for products which got sales both lat year and this year.

i have tried this using set analysis as given below.

Sum({<Sales= p({<Sales= {"=Sum({1<Year={$(=Max(Year))}>}Sales)>0"}>})*

p({<Sales= {"=Sum({1<Year={$(=Max(Year)-1)}>}Sales)>0"}>})>}Sales)

but the above expression calculating sales for all the products irrespective of the P() condition.

Can someone throw some light around the issue here.

Thanks in advance,

Regards,

Nani

[antoniotiman]

Hi Nani,

maybe

Sum({<Product= p({<Product= {"=Sum({1<Year={$(=Max(Year))}>}Sales)>0"}>})*

p({<Product= {"=Sum({1<Year={$(=Max(Year)-1)}>}Sales)>0"}>})>}Sales)

[sunny_talwar]

May be just this

Sum({<Product = {"=Sum({1<Year = {$(=Max(Year))}>} Sales) > 0 and Sum({1<Year = {$(=Max(Year) - 1)}>} Sales) > 0"}>} Sales)

[effinty2112]

Hi Nani,

Try:

Sum({$<ProdID = p({<Year = {$(=max(Year))}>})>*<ProdID = p({<Year = {$(=-1+max(Year))}>})>}Sales)

Where ProdID identifies products.

good luck

Andrew

[#5]

Thanks all for the responses. I am trying everyone's solutions. will update everyone once i got the right solution.

Thanks again.

Regards,

Nani

[#6]

@antoniotiman‌ and @stalwar1 both your answers are giving same result but still i am getting some products in my bar chart which dont' have sales either last year and this year.

i am trying to understand if i miss anything here.

Thanks,

Nani

[sunny_talwar]

When you say this year? do you mean the selected year and last from the selected year?

[#8]

No i don't have any year filter.This year means current year irrespective of any selection.

[sunny_talwar]

May be this

Sum({<Product = {"=Sum({1<Year = {$(=Year(Today()))}>} Sales) > 0 and Sum({1<Year = {$(=Year(Today()) - 1)}>} Sales) > 0"}>} Sales)

[#10]

Still same results getting few products which doesn't have sales.