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

[alexandros17]

If category is a field it will never work because set analysis can't compare a field with another field. In set analysis yu can't use if statement:.

Add your example and I'll help you

[#3]

Try some thing like

Sum({$<year={$(=(year(today())-1))},Brand={'category'}>}Value)

[#4]

Hi,

Try this,

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

[#5]

You cannot use IF in the set analysis, and if you want to compare two fields (A=B) you need a third one. here, I have added a BrandID field to show you.

For the year, you must get the result of your expression, and afterwards do -1

Please try:

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

Fabrice

[santharubban]

try this

({$<Year={'$(=Only(Year)-1 )'}, Brand={'$(category)'}>}Value)

or

({$<Year={"$(=Only(Year)-1 )"}, Brand={'$(category)'}>}Value)

[#7]

or Brand = p(Category)

Fabrice

[#8]

Hi Aunez,

It is two fields.

What I have is sales data which has Category and target by brands.  Both are same ie Product A in both

What I want to do is put them in a table so you haveLike this:

                  Sales        Target

Brand A

Brand B

This issue I get at current is that it does not brek the target down by Brand uust sums a total hence the brand equals category.

This statemnt I curently havein

Sum

({$<year={$(=(year(today())-1))},Brand = p(Category)>}Value)

Many thanks

Paul

[#9]

No

QlikView computes the set analysis only ONCE, before computing the chart

it cannot be broken by rows

You should go back to a sum(if ....)

Fabrice

[#10]

Okay have gone back to

sum

(if(Brand = Category ,Value))

and I get this years sales which is great but I now wish to show last years sales.

I tried

sum

(if(Brand = Category and Year='2013',Value))  but I dont get any results.

Thanks for the help.