Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

this should even work with pure text:

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

View solution in original post

12 Replies
sunny_talwar

May be you need an equal sign?

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

vinieme12
Champion III
Champion III

make sure  the field is formatted as NUM  DYEARWEEK

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

Thanks Sunny, this is returning 0

Not applicable
Author

thank you Vineeth, it is set as a number

Anonymous
Not applicable
Author

this should even work with pure text:

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

Not applicable
Author

Ace! Thank you Robin.

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

Anonymous
Not applicable
Author

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

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
Author

I've tried the following but returning 0

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