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: 
adamdavi3s
Master
Master

Help with set analysis / comparative data / generating list of values

Hi All,

I am trying to create a specific piece of analysis within an existing dashboard but I am stuck at one bit.

The attached is obviously hugely simplified and the logic behind the two distinct selection boxes and data island for trust are set in stone.

Included in the data is an ID, then a READ ID which signifies a readmission in this case.

The READ ID is an existing ID, but I need to show against the original ID the count of READ ID.

This works great and I can count these fine.

What I then want to do, is create a list of the READ ID which I can then use in set analysis to produce a sum of the cost associated with READ ID when READ ID = ID

This works perfectly for my TRUST A selections, but I just can't get the set analysis right to generate the list for TRUST B. I am sure it is something really simple but I just can't sus it out!

Many thanks in advance for the help, hopefully the attached makes more sense.

10 Replies
Ferran_Garcia_Pagans
Former Employee
Former Employee

Hi Adam,

I attach a workarround. I used alternate state to solve it.

Hope this help,

Ferran

jolivares
Specialist
Specialist

I think you need to flag your data using the trust B too, now i can see just the trust A en the trust B "is out of" the data set.

Try to flag it, because trust A = trust B.  Or explain better your case.

adamdavi3s
Master
Master
Author

Hi Ferran,

Thank you, I wish I could use alternative states!

Unfortunately we still have some customers on old versions, even back to version 9

adamdavi3s
Master
Master
Author

Hi Juan,

Trust B is out of the data set as a data island. This is so we can compare the same data with itself.

TrustA and TrustB are exactly the same trusts linked to the fact data, but we want to be able to compare the data for a group vs another group and this was the way to do it.


The model is such that it can handle the data (approx 14m rows by 30 odd columns in the fact) at a sensible speed.

Obviously as Ferran points out, the alternative states is the proper way to do this but that isn't possible in this case

Not applicable

Not sure if this will entirely solve your issue but maybe try using

v_read_id_b = chr(39)&concat({<TRUST = {$(TRUSTB)}, DAYS ={"<=$(v_readmission_time)"}>} READ_ID,Chr(39)&chr(44)&chr(39))&Chr(39). Just a slight variation on what you have already but it seems to work by avoiding the aggr function.

adamdavi3s
Master
Master
Author

Spot on thank you... no idea how I didn't get that!

Not actually sure why I was using aggr and only anyway.... I think I didn't realise you can just do set analysis within a CONCAT... I thought it required an aggregation formula, which I guess concat is anyway

Thank you

adamdavi3s
Master
Master
Author

Hmmm, for some reason it doesn't work on my upscaled model (which is too huge to upload) and I can't suss out why

adamdavi3s
Master
Master
Author

Nearly there... but still no banana...

Can anyone explain why when I create a calculated dimension =v_read_id_b it just lists all the column names in the data model?

I can't figure out if this is what is causing the issue in the scaled up version... see the attached...

Not applicable

Sorry for the slow reply, have been a little busy.

I'm unsure exactly what you are after with the calculated dimension here, do you want it to list only the IDs associated with the selections in TRUSTB?

Slightly silly but have you tried using =$(v_read_id_b) as the dimension?