topic Re: sum between values in QlikView

sum between values

Mon, 05 Jun 2017 12:11:19 GMT

Can anyone see what i'd doing wrong here? I am trying to sum the value where the DYEARWEEK (201713 format) is between the StartYrWk and EndYrWk (current selection has the StartYrWk at 201713 and the EndYrWk at 201714.

=sum({<DYEARWEEK = {">=$(StartYrWk)<=$(EndYrWk)"}>}Value)

in other expressions I use p but this doesn't seem to work where you have set analysis following =p

Currently the result of the above is the total sum of the value disregarding the selected ranges.

Any suggestions would be appreciated.

Re: sum between values

sunny_talwar — Mon, 05 Jun 2017 12:12:53 GMT

May be you need an equal sign?

=Sum({<DYEARWEEK = {">=$(=StartYrWk)<=$(=EndYrWk)"}>}Value)

Re: sum between values

vinieme12 — Mon, 05 Jun 2017 12:13:50 GMT

make sure the field is formatted as NUM DYEARWEEK

Re: sum between values

Mon, 05 Jun 2017 12:16:55 GMT

Thanks Sunny, this is returning 0

Re: sum between values

Mon, 05 Jun 2017 12:33:26 GMT

thank you Vineeth, it is set as a number

Re: sum between values

Anonymous — Mon, 05 Jun 2017 12:39:25 GMT

this should even work with pure text:

=Sum({<DYEARWEEK = {"= (DYEARWEEK>=$(=StartYrWk)) and (DYEARWEEK<=$(=EndYrWk))"}>}Value)

Re: sum between values

Mon, 05 Jun 2017 12:44:22 GMT

Ace! Thank you Robin.

And thank you everyone else for your suggestions. - most helpful.

Re: sum between values

Anonymous — Mon, 05 Jun 2017 12:48:54 GMT

... you could try in script

(it's not about formatting, it's about numerical representation)

LOAD

...

num(num#(DYEARWEEK)) as DYEARWEEK;

...

then Sunny's solutions should also work

--> numerical search (=Sunny's solutions) is much faster than expression search (= my solution)

Re: sum between values

Mon, 05 Jun 2017 13:21:56 GMT

If i wanted to use the same expression but switch out the StartYrWk/EndYeWk for the YEARWEEK associated with two alternate states ([Start], [End]), do you know how this would be written?

Re: sum between values

Mon, 05 Jun 2017 14:03:51 GMT

I've tried the following but returning 0

=Sum({<DYEARWEEK = {"= (DYEARWEEK>=p(=[Start]YEARWEEK)) and (DYEARWEEK<=p(=[End]YEARWEEK)"}>}Demand)

Re: sum between values

sunny_talwar — Mon, 05 Jun 2017 14:08:40 GMT

May be this

=Sum({<DYEARWEEK = {"=(DYEARWEEK >= Only({[Start]} YEARWEEK)) and (DYEARWEEK <= Only({[End]} YEARWEEK))"}>}Demand)

Re: sum between values

Mon, 05 Jun 2017 14:13:05 GMT

Perfect, thank you so much

Re: sum between values

sunny_talwar — Mon, 05 Jun 2017 14:14:30 GMT

Hahahaha I see you fixed the helpful response thing.... I am glad it worked