Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
this should even work with pure text:
=Sum({<DYEARWEEK = {"= (DYEARWEEK>=$(=StartYrWk)) and (DYEARWEEK<=$(=EndYrWk))"}>}Value)
May be you need an equal sign?
=Sum({<DYEARWEEK = {">=$(=StartYrWk)<=$(=EndYrWk)"}>}Value)
make sure the field is formatted as NUM DYEARWEEK
Thanks Sunny, this is returning 0
thank you Vineeth, it is set as a number
this should even work with pure text:
=Sum({<DYEARWEEK = {"= (DYEARWEEK>=$(=StartYrWk)) and (DYEARWEEK<=$(=EndYrWk))"}>}Value)
Ace! Thank you Robin.
And thank you everyone else for your suggestions. - most helpful.
... 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)
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?
I've tried the following but returning 0
=Sum({<DYEARWEEK = {"= (DYEARWEEK>=p(=[Start]YEARWEEK)) and (DYEARWEEK<=p(=[End]YEARWEEK)"}>}Demand)