sum between values

[#1]

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.

[sunny_talwar]

May be you need an equal sign?

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

[vinieme12]

make sure  the field is formatted as NUM  DYEARWEEK

[#4]

Thanks Sunny, this is returning 0

[#5]

thank you Vineeth, it is set as a number

[Anonymous · Accepted solution]

this should even work with pure text:

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

[#7]

Ace! Thank you Robin.

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

[Anonymous]

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

[#9]

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?

[#10]

I've tried the following but returning 0

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