Set Analysis - Dynamic Record Set

[ejacob]

Hi,

What's wrong with this Dynamic Record Set, based on the user's current selections?

=Sum({$<month_cal={'$(=Month(AddMonths(Makedate(year_cal, Num(month_cal,'00')),-1)))'}>} DISTINCT active_employees)

Thanks.

[ejacob]

I'll try to explain better what I need with this analysis

ejacob escreveu:

Hi,

What's wrong with this Dynamic Record Set, based on the user's current selections?

=Sum({$<month_cal={'$(=Month(AddMonths(Makedate(year_cal, Num(month_cal,'00')),-1)))'}>} DISTINCT active_employees)

Thanks.

I have this values

month_cal	active_employees	set_analysis calc
jan	155	december value - year before
feb	203	155
mar	205	203

I can't use Above(), because it's a bar chart.

[#3]

what is the error you're getting?

[ejacob]

When I use this code on set_analysis_calc expression

=Month(AddMonths(Makedate(year_cal, Num(month_cal,'00')),-1))

I have this values

month_cal	active_employees	set_analysis calc
jan	155	dec
feb	203	jan
mar	205	feb

But the entire code

=Sum({$<month_cal={'$(=Month(AddMonths(Makedate(year_cal, Num(month_cal,'00')),-1)))'}>} DISTINCT active_employees)

Results on something like this

month_cal	active_employees	set_analysis calc
jan	155	0
feb	203	0
mar	205	0

[#5]

Try this

=Sum({$<month_cal={$(=Chr(39) & Month(AddMonths(Makedate(year_cal, Num(month_cal,'00')),-1))) & Chr(39)}>} DISTINCT active_employees)

[ejacob]

Hi. Unfortunately, didn´t work.

[jerem1234 · Accepted solution]

It is not working because set analysis is calculated once per chart and not per row. Therefore it will not change dynamically with each row. You'll have to use the above() function or create a field in loadscript that uses peek() or previous().

Hope this helps!

[ejacob]

Now It works. Thanks.

[IAMDV]

I've made video tutorials on Previous() and Peek(). Just in case if you need them.

http://qlikshare.com/184/

http://qlikshare.com/172/

Thanks,

DV

www.QlikShare.com