topic Re: Help with set analysis / comparative data / generating list of values in QlikView

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

adamdavi3s — Sun, 30 Mar 2014 21:17:09 GMT

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.

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

Ferran_Garcia_Pagans — Sun, 30 Mar 2014 22:27:44 GMT

Hi Adam,

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

Hope this help,

Ferran

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

jolivares — Sun, 30 Mar 2014 23:18:16 GMT

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.

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

adamdavi3s — Mon, 31 Mar 2014 06:44:40 GMT

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

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

adamdavi3s — Mon, 31 Mar 2014 06:47:31 GMT

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

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

Mon, 31 Mar 2014 08:25:51 GMT

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.

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

adamdavi3s — Mon, 31 Mar 2014 08:30:39 GMT

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

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

adamdavi3s — Mon, 31 Mar 2014 17:36:29 GMT

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

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

adamdavi3s — Mon, 31 Mar 2014 18:57:36 GMT

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

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

Tue, 01 Apr 2014 08:10:06 GMT

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?

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

adamdavi3s — Tue, 01 Apr 2014 08:14:27 GMT

Hi,

Not a problem in the slightest, I really appreciate the help thank you!

I had, but only in the full version which didn't work although it does work perfectly in the cut down version.

I think there must be something additional in the full version which is causing the issue so I will see if I can re-create the issue by building more into the cut down

Thank you for all the help so far! I only unmarked your answer as correct and changed it to helpful so it didn't show as answered, no offence was meant by doing that!