Strangely simple sum not working

[#1]

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

[#2]

Hi,

This should work.

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

Regards,

Janzen

[Anonymous]

Hi Nick,

Try this instead:

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

[Anonymous]

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.

[#5]

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

[#6]

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

[#7]

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

[#8]

Thankyou 🙂

[#9]

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 !!!!

[whiteline · Accepted solution]

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)