Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

sum between values

Hi

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.

1 Solution

Accepted Solutions
robin_hausdoerfer
Valued Contributor III

Re: sum between values

this should even work with pure text:

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

View solution in original post

12 Replies

Re: sum between values

May be you need an equal sign?

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

vinieme12
Esteemed Contributor II

Re: sum between values

make sure  the field is formatted as NUM  DYEARWEEK

Not applicable

Re: sum between values

Thanks Sunny, this is returning 0

Not applicable

Re: sum between values

thank you Vineeth, it is set as a number

robin_hausdoerfer
Valued Contributor III

Re: sum between values

this should even work with pure text:

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

View solution in original post

Not applicable

Re: sum between values

Ace! Thank you Robin.

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

robin_hausdoerfer
Valued Contributor III

Re: sum between values

... 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)

Not applicable

Re: sum between values

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?

Not applicable

Re: sum between values

I've tried the following but returning 0

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