topic Complex Set Analysis in QlikView

Complex Set Analysis

rudywelvaert — Tue, 24 Apr 2018 14:13:23 GMT

Hi,

I like to write a set analysis to replace following expression:

=Sum(If(AMOUNT_DOSSIER > 25000
and FUNCTION='ABC'
and (QUARTER_COUNT = $(vYearCount) & ' Q4' or DATE_COUNT = $(vLastDateCount))
and YEAR_COUNT=$(vYearCount)
, NUMBER_DOSSIERS))

Can this be done? And how?

Thanks

R.W.

Re: Complex Set Analysis

sunny_talwar — Tue, 24 Apr 2018 14:15:56 GMT

May be this

=Sum({<AMOUNT_DOSSIER = {">25000"}, FUNCTION = {'ABC'}, QUARTER_COUNT = {"$(=$(vYearCount) & ' Q4')", {"$(=$(vLastDateCount))"}, YEAR_COUNT = {"$(=$(vYearCount))"}>} NUMBER_DOSSIERS)

Re: Complex Set Analysis

vishsaggi — Tue, 24 Apr 2018 14:24:39 GMT

Sunny for vLastDateCount they are using a Date_Count field? Do not we need to add that field too in the set?

Re: Complex Set Analysis

rudywelvaert — Tue, 24 Apr 2018 14:25:45 GMT

Sunny,

Thanks

But this doesn't work.

The field DATE_COUNT is missing

or DATE_COUNT = $(vLastDateCount)

There is an or statement that tests on two different fields: QUARTER_COUNT and DATE_COUNT

R.W.

Re: Complex Set Analysis

vishsaggi — Tue, 24 Apr 2018 14:31:49 GMT

Extending Sunny's expr: Just add DATE_COUNT before vLastDateCount variable

=Sum({< AMOUNT_DOSSIER = {">25000"},
FUNCTION = {'ABC'},
QUARTER_COUNT = {"$(=$(vYearCount) & ' Q4')"},
DATE_COUNT = {"$(=$(vLastDateCount))"},
YEAR_COUNT = {"$(=$(vYearCount))"}
>} NUMBER_DOSSIERS
)

The above will not work

Follow Sunny's Set expr using operators.

Re: Complex Set Analysis

sunny_talwar — Tue, 24 Apr 2018 14:32:08 GMT

Yes, you are right .. may be this

=Sum({

<AMOUNT_DOSSIER = {">25000"}, FUNCTION = {'ABC'}, QUARTER_COUNT = {"$(=$(vYearCount)& ' Q4')"}, YEAR_COUNT = {"$(=$(vYearCount))"}>

<AMOUNT_DOSSIER = {">25000"}, FUNCTION = {'ABC'}, DATE_COUNT = {"$(=$(vLastDateCount))"}, YEAR_COUNT = {"$(=$(vYearCount))"}>

} NUMBER_DOSSIERS)

Re: Complex Set Analysis

vishsaggi — Tue, 24 Apr 2018 14:33:59 GMT

IS this because of OR ? So we cannot give just datecount right bcoz Set analysis default AND? Correct me?

Re: Complex Set Analysis

sunny_talwar — Tue, 24 Apr 2018 14:34:25 GMT

Yes