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

[adamdavi3s]

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.

[Ferran_Garcia_Pagans]

Hi Adam,

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

Hope this help,

Ferran

[jolivares]

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]

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]

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

[#6]

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]

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]

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]

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...

[#10]

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?