topic Re: Set Analysis Help Needed in QlikView

Set Analysis Help Needed

saumyashah90 — Sat, 16 Nov 2024 00:19:13 GMT

Hi Team,

I have EID,Date(multiple months) and TeamCode.

If i want to check if TeamCode or that EID for that month is same as previous or mentioned month , how do i do it in set anlaysis.

I want count of those EID, whose teamcode for that month is not same as previous/variable month.

Re: Set Analysis Help Needed

stevedark — Wed, 15 Jul 2020 09:58:59 GMT

I would create variables for This Month and Prior Month, and add a counter (1 as EIDCount) in the load.

Then it would be something like:

sum({<Month*={'$(vThisMonth')},TeamCode-={'$(=maxstring({<Month={'$(vPriorMonth)'}>}TeamCode))'}>}EIDCount)

Getting the format of the Month correct in the variables (using Date()) is critical.

Hope that helps.

Re: Set Analysis Help Needed

MakMakesitpossible — Wed, 15 Jul 2020 12:42:26 GMT

Steve can you explain us what is the purpose of Month*

Re: Set Analysis Help Needed

stevedark — Wed, 15 Jul 2020 12:50:14 GMT

Using *= in set analysis, rather than just = means that the current selection is taken into account for that filter, rather than ignoring the users selection. This makes for a much more intuitive experience for the user.

I wrote a blog post on the differences between the two approaches that you may find interesting:

https://www.quickintelligence.co.uk/qlik-set-analysis-star-equals/

Cheers,

Steve

Re: Set Analysis Help Needed

MakMakesitpossible — Wed, 15 Jul 2020 13:00:11 GMT

Thank you Steve....as always inspiring 🙂

Re: Set Analysis Help Needed

saumyashah90 — Thu, 16 Jul 2020 01:37:01 GMT

Hi Steve,

I dont have flexibility to change anything in the script. Need to do in UI only

Re: Set Analysis Help Needed

stevedark — Thu, 16 Jul 2020 06:58:03 GMT

That is a shame, as adding a counter to a table can make all your charts and tables much more efficient in the front end application. The code above though could be rewritten to use a COUNT function instead:

count({<Month*={'$(vThisMonth')},TeamCode-={'$(=maxstring({<Month={'$(vPriorMonth)'}>}TeamCode))'}>}EID)

That should do it.