Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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)