Issue with previous month set analysis

[#1]

Hi,

I have an issue with getting a previous month set analysis formula. Have been reading the different topics on this without success.

I wish to build a dynamic previous date function that will receive a value from the previous month. If the user chose period 201403, the value for 201402 should appear.

Period is YYYYMM format. I get the previous month by using: Date#(Period, 'YYYYMM')-1

However, I'm not able to get any values on the previous month, I'm using this set analysis:

=sum({$<Period = {"$(=Date#(Period,'YYYYMM')-1)"}>} Amount)

Thankful for any input on how to get this correct.

/Niklas

[tresesco]

Set analysis doesn't evaluate row-wise. Try :

= Above(Sum(Amount))

[#3]

Hi,

Thanks for your reply. Let me be more precise; the
end result should dynamically show the % - change between the current and
previous month, preferably in a KPI text-box. Ex. When the user selects 201403,
the % change from 201402 should automaticcaly appear. That’s why I'm interested
in getting the set-analysis above to work.

Any input on how to solve this? I’m really struggling
to see why the set analysis does not work properly.

/Nikals

[sasikanth]

hi

LOAD  Date#(Period,'YYYYMM') as Period ,
     Amount
FROM
D:\QV\CanalDigital\TESTQV.xlsx
(ooxml, embedded labels);

load like above and create a variable

Let v1=max(Period)

then try the expression

sum({<Period={'$(=v1-1)'}>}Amount}

may be helpfull

[maxgro · Accepted solution]

in a text box the sum of previous period should be (just replace your Date# with Date)

=sum({$<Period = {"$(=Date(Period,'YYYYMM')-1)"}>} Amount)

[#6]

Thanks, that was it.

/N