topic Re: Strangely simple sum not working in QlikView

Strangely simple sum not working

Mon, 22 Oct 2012 08:38:20 GMT

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 !

Thanks in advance

Nick

Re: Strangely simple sum not working

Mon, 22 Oct 2012 08:43:42 GMT

Hi,

This should work.

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

Regards,

Janzen

Re: Strangely simple sum not working

Anonymous — Mon, 22 Oct 2012 08:45:21 GMT

Hi Nick,

Try this instead:

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

Re: Strangely simple sum not working

Anonymous — Mon, 22 Oct 2012 08:47:32 GMT

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

Mon, 22 Oct 2012 08:49:00 GMT

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

Re: Strangely simple sum not working

Mon, 22 Oct 2012 08:49:09 GMT

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

Re: Strangely simple sum not working

Mon, 22 Oct 2012 08:56:20 GMT

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

Mon, 22 Oct 2012 08:59:35 GMT

Thankyou 🙂

Re: Strangely simple sum not working

Fri, 02 Nov 2012 13:15:44 GMT

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

whiteline — Sat, 03 Nov 2012 10:37:39 GMT

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

Mon, 05 Nov 2012 09:21:07 GMT

Thankyou !!!!!!!

Re: Strangely simple sum not working

Fri, 25 Jan 2013 11:59:57 GMT

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

Fri, 25 Jan 2013 13:40:43 GMT

This didn;t work 😞 but thanks for trying

Re: Strangely simple sum not working

Fri, 25 Jan 2013 15:09:04 GMT

sorted thanks - changed to num in the script !