topic Re: Sum if statement in QlikView

Sum if statement

Tue, 18 Feb 2014 13:20:15 GMT

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

Re: Sum if statement

alexandros17 — Tue, 18 Feb 2014 13:23:29 GMT

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

Re: Sum if statement

Tue, 18 Feb 2014 13:25:02 GMT

Try some thing like

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

Re: Sum if statement

Tue, 18 Feb 2014 13:28:29 GMT

Hi,

Try this,

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

Re: Sum if statement

Tue, 18 Feb 2014 13:32:48 GMT

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

Re: Sum if statement

santharubban — Tue, 18 Feb 2014 14:55:47 GMT

try this

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

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

Re: Sum if statement

Tue, 18 Feb 2014 15:09:20 GMT

or Brand = p(Category)

Fabrice

Re: Sum if statement

Tue, 18 Feb 2014 15:25:26 GMT

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

Re: Sum if statement

Tue, 18 Feb 2014 15:34:33 GMT

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

Re: Sum if statement

Tue, 18 Feb 2014 15:47:56 GMT

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.

Re: Sum if statement

Tue, 18 Feb 2014 15:54:08 GMT

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

Re: Sum if statement

Tue, 18 Feb 2014 16:25:02 GMT

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.

Re: Sum if statement

Tue, 18 Feb 2014 16:52:39 GMT

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