topic Re: Set Analysis - Dynamic Record Set in QlikView

Set Analysis - Dynamic Record Set

ejacob — Tue, 17 Jun 2014 03:00:25 GMT

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.

Re: Set Analysis - Dynamic Record Set

ejacob — Tue, 17 Jun 2014 03:23:37 GMT

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.

Re: Set Analysis - Dynamic Record Set

Tue, 17 Jun 2014 03:23:39 GMT

what is the error you're getting?

Re: Set Analysis - Dynamic Record Set

ejacob — Tue, 17 Jun 2014 03:47:29 GMT

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

Re: Set Analysis - Dynamic Record Set

Tue, 17 Jun 2014 04:43:33 GMT

Try this

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

Re: Set Analysis - Dynamic Record Set

ejacob — Tue, 17 Jun 2014 15:04:05 GMT

Hi. Unfortunately, didn´t work.

Re: Set Analysis - Dynamic Record Set

jerem1234 — Tue, 17 Jun 2014 15:11:42 GMT

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!

Re: Set Analysis - Dynamic Record Set

ejacob — Wed, 18 Jun 2014 13:37:24 GMT

Now It works. Thanks.

Re: Set Analysis - Dynamic Record Set

IAMDV — Wed, 18 Jun 2014 13:47:43 GMT

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,