1 Reply Latest reply on May 2, 2018 10:23 AM by Michel Caissie

# Conditional Sum Calculation

Dear Experts,

I am struggling with creating a calculated field, which should return a country total sales volume before introducing a product in each district (ID). For example, in the table below the outcomes should be for 50 for CA01, 0 for CA02, 258 for CA03, etc. I suspect that the expression should look similar to {FIXED[Country]:SUM(IF {FIXED [ID]:MIN([Month])}<[Month] THEN [Sales] END)}, but something is wrong here, as obviously I am confused with LoD and conditional sum parameters. Would appreciate your help. The packaged book is attached (ver. 10.5.3).

Thank you,

Sergey

• ###### 1. Re: Conditional Sum Calculation

Sergey,

check in the attached...

My final calculation is

IFNULL({FIXED [ID]: SUM( if [Month]<[min Month]  THEN [Country Monthly Sales] END) },0)

where Country Monthly Sales is

{FIXED [Country],[Month]: SUM( Sales )}

min Month is

{FIXED [ID]: MIN(if [ID Monthly Sales] > 0 then [Month] end)}

and ID Monthly Sales is

{FIXED [ID],[Month]: SUM(Sales)}

Michel

1 of 1 people found this helpful