Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rudywelvaert
Creator
Creator

Complex Set Analysis

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.



7 Replies
sunny_talwar

May be this

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

vishsaggi
Champion III
Champion III

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

rudywelvaert
Creator
Creator
Author

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.


vishsaggi
Champion III
Champion III

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.

sunny_talwar

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)

vishsaggi
Champion III
Champion III

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

sunny_talwar

Yes