Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions
MVP
MVP

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)

5 Replies
MVP
MVP

Re: Issue with previous month set analysis

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

= Above(Sum(Amount))

Not applicable

Re: Issue with previous month set analysis

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
Valued Contributor III

Re: Issue with previous month set analysis

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

MVP
MVP

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)

Not applicable

Re: Issue with previous month set analysis

Thanks, that was it.

/N

Community Browser