13 Replies Latest reply: Jan 25, 2013 10:09 AM by N G

# Strangely simple sum not working

HI,

I cannot get what should be a really simple sum to work. In the attached you will see a simple branch, week, budget table.

Using a variable 'thisweek' I am just trying to get sum to show if WEEK = thisweek sum the budget, but it is not working.

Am I missing something obvious please as this is driving me nuts !

Nick

• ###### Re: Strangely simple sum not working

Hi,

This should work.

Sum ({<Week = {\$(ThisWeek)}>}Budget)

Regards,

Janzen

• ###### Re: Strangely simple sum not working

Thankyou both - not only a solution but also a logic as well :-)

• ###### Re: Strangely simple sum not working

Hi Nick,

Sum({\$<WEEK={\$(ThisWeek)}>}Budget)

• ###### Re: Strangely simple sum not working

Heh, I thought I was fast.

Anyway, here's why it doesn't work (also interesting ):

If you haven't made a single selection in WEEK, using the WEEK without any aggregation function around it will return null, so in this case you're trying to match null with 43 which doesn't compute for QlikView:

Sum (if(WEEK=ThisWeek,Budget))

if you enclosed it in an aggr() function aggregating over WEEK and summing that up it would work but it's a lot cleaner and probably faster with a simple set analysis.

• ###### Re: Strangely simple sum not working

Thankyou both - not only a solution but also a logic as well :-)

• ###### Re: Strangely simple sum not working

Hi Nick,

Instead of  Sum(if(WEEK=ThisWeek,Budget)), try to use this expression Sum({\$<WEEK={\$(ThisWeek)}>} Budget)

Hopefully it will work for you.

Carmen

• ###### Re: Strangely simple sum not working

Thankyou :-)

• ###### Re: Strangely simple sum not working

New problem

The code works unless I now try to do week to date this year i.e.

Sum if WEEK <= This Week, Budget

Sum({\$<WEEK={\$(ThisWeek)}>}Budget)

Tried this syntax

Sum ({<WEEK = {"<=\$(ThisWeek)"}>}Budget)

but again I get no result... any ideas pease ?

Thanks again !!!!

• ###### Re: Strangely simple sum not working

Hi.

For this to work your WEEK should be a number while in your data it' just a string (the easiest way to note: it's aligned to the left in list boxes while numbers - to the right, by default).

You could convert WEEK to number on load or during a qvd creation with Num#() function or use slightly different set analysis expression:

Sum({\$<WEEK={"=Num#(WEEK)<=\$(ThisWeek)"}>}Budget)

• ###### Re: Strangely simple sum not working

Thankyou !!!!!!!

• ###### Re: Strangely simple sum not working

HI All,

As a follow on, I cannot seem to get this to work with multiple weeks though... the below does not seem to work

((Sum({\$<WEEK={"(=Num#(WEEK) >=\$(StartWeek) <=(ThisWeek)) "}>} Budget)))

does anone have any ideas  please?

Thanks again

Bob

• ###### Re: Strangely simple sum not working

This didn;t work :-( but thanks for trying

• ###### Re: Strangely simple sum not working

sorted thanks - changed to num in the script !