5 Replies Latest reply: Jun 28, 2014 5:17 AM by Niklas Krysander

# Issue with previous month set analysis

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

• ###### Re: Issue with previous month set analysis

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

= Above(Sum(Amount))

• ###### Re: Issue with previous month set analysis

Hi,

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

• ###### Re: Issue with previous month set analysis

hi

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}

• ###### Re: Issue with previous month set analysis

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

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

• ###### Re: Issue with previous month set analysis

Thanks, that was it.

/N