Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
in a text box the sum of previous period should be (just replace your Date# with Date)
=sum({$<Period = {"$(=Date(Period,'YYYYMM')-1)"}>} Amount)
Set analysis doesn't evaluate row-wise. Try :
= Above(Sum(Amount))
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
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
in a text box the sum of previous period should be (just replace your Date# with Date)
=sum({$<Period = {"$(=Date(Period,'YYYYMM')-1)"}>} Amount)
Thanks, that was it.
/N