topic Re: Issue with previous month set analysis in QlikView

Issue with previous month set analysis

Thu, 26 Jun 2014 07:21:13 GMT

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

tresB — Thu, 26 Jun 2014 07:30:11 GMT

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

= Above(Sum(Amount))

Re: Issue with previous month set analysis

Fri, 27 Jun 2014 13:14:11 GMT

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

Re: Issue with previous month set analysis

sasikanth — Fri, 27 Jun 2014 13:47:45 GMT

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

Re: Issue with previous month set analysis

maxgro — Fri, 27 Jun 2014 20:04:12 GMT

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

Sat, 28 Jun 2014 09:17:46 GMT

Thanks, that was it.