Set Analysis Help Needed

[saumyashah90]

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.

[stevedark]

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.

[MakMakesitpossible]

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

[stevedark]

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

[MakMakesitpossible]

Thank you Steve....as always inspiring 🙂

[saumyashah90]

Hi Steve,

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

[stevedark · Accepted solution]

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.