Sum if statement

[#1]

Hi I am trying to get a sum statment to work.

See below I want to sum value if brand =category and year is previous year to selected.

I have tried a few way around it but cant seem to get it too work. Any suggestions welcome.

Sum

({$<Year={$(=Only(Year-1))},if(Brand=Category)>}Value)

Thanks

Paul

[#2]

i would do,

if(Brand=Category, sum({<Year = {"$(=max(Year)-1)"}>  } Sales)

The set analysis must be in an aggregation function (therefore sum is OK)

It is computed only ONCE (we want to get the previous year, always the same: it is OK)

You can replace Max with Only if you have only one year selected by the user.

Tell me

Fabrice

[#3]

Thanks the sum({<Year = {"$(=max(Year)-1)"}>  } Sales) works fine but as soon as I add the If statement I dont get any results..  If I do sum(if(Brand=Category) I do get data but only for vcurrent year.

[#4]

Of course !!

Brand is unique (because one row per brand)

But Category is not unique. So if you do such comparison Brand (unique) = Category (not unique) , it will be false

You will have perhaps add a flag (1/0) in the appropriate table

After that you will have the choice to use the flag where you want:

in the expression : Sales*Flag

in the IF (Flag = 1, ....)

in the set : sum( {< Flag={1}, Year = {"$(=max(Year)-1)"}> } Sales)

The last one is quicker.

Fabrice