topic Re: Re: Re: Ignore Selections in QlikView

Ignore Selections

tchovanec — Mon, 28 Oct 2013 19:38:43 GMT

I have the following expression and I would like to ignore the ISLAND_DEPT.DEPT_DESC selection and show the data for all depts.



SUM(AGGR(IF(FirstSortedValue(
 {<
 ADMIT_DT ={">= $(=NUM(ADDMONTHS(MONTHSTART(MAX(ISLAND_CALENDAR.DATE)),-12))) <=$(=MAX(ISLAND_CALENDAR.DATE))"},
 FLG_Z_PATIENTS = {0},
 >}
DISTINCT DEPT_ABBR,-ADMIT_DTTM)= ISLAND_DEPT.DEPT_ABBR,1,0),MRN,ISLAND_DEPT.DEPT_ABBR))

I have tried the following without any success.

SUM(AGGR(IF(FirstSortedValue(
{1<
ADMIT_DT ={">= $(=NUM(ADDMONTHS(MONTHSTART(MAX(ISLAND_CALENDAR.DATE)),-12))) <=$(=MAX(ISLAND_CALENDAR.DATE))"},
FLG_Z_PATIENTS = {0},
ISLAND_DEPT.DEPT_DESC=
>}
DISTINCT DEPT_ABBR,-ADMIT_DTTM)= ISLAND_DEPT.DEPT_ABBR,1,0),MRN,ISLAND_DEPT.DEPT_ABBR))

Any idea why adding the 1 or ISLAND_DEPT.DEPT_DESC does not work. Any ideas on how to get this to work would be greatly appreciated. Thank you.

Re: Ignore Selections

CELAMBARASAN — Mon, 28 Oct 2013 19:42:14 GMT

You should use it the Sum too

Try this

SUM({< ISLAND_DEPT.DEPT_DESC= >}

AGGR(IF(FirstSortedValue(
{<
ADMIT_DT ={">= $(=NUM(ADDMONTHS(MONTHSTART(MAX(ISLAND_CALENDAR.DATE)),-12))) <=$(=MAX(ISLAND_CALENDAR.DATE))"},
FLG_Z_PATIENTS = {0},
ISLAND_DEPT.DEPT_DESC=
>}
DISTINCT DEPT_ABBR,-ADMIT_DTTM)= ISLAND_DEPT.DEPT_ABBR,1,0),MRN,ISLAND_DEPT.DEPT_ABBR))

Re: Ignore Selections

tchovanec — Mon, 28 Oct 2013 19:45:24 GMT

That still returned only the 1 selected department and not every dept.

Re: Ignore Selections

Anonymous — Mon, 28 Oct 2013 19:48:39 GMT

Thomas,

Are you using this expression in a chart? If so, the dimensions of the chart may be dependent on this selection.

Re: Ignore Selections

tchovanec — Mon, 28 Oct 2013 19:51:14 GMT

I am using it in a straight table and the only dimension I have is the ISLAND_DEPT.DEPT_ABBR

Re: Ignore Selections

Anonymous — Mon, 28 Oct 2013 20:28:19 GMT

Try to check "show all values". If it doesn't help, need to see your file...

Re: Re: Ignore Selections

Anonymous — Tue, 29 Oct 2013 13:14:38 GMT

50 MB is too much...

Could you please upload a reduced version? One MB or less?

Re: Re: Re: Ignore Selections

tchovanec — Tue, 29 Oct 2013 14:22:54 GMT

I have attached an updated version. What I would like to happen is when a dept is chosen from the list box the table will still show all the dept values. Thank you for the assistance.

Re: Re: Re: Ignore Selections

Anonymous — Tue, 29 Oct 2013 14:57:32 GMT

Thomas,

If follow your requirement literally, it is enough to uncheck "suppress zero-values" on Presentation tab, and to check "show all values" on Dimensions tab. Makes sense to check "suppress when value is null" to remove the "-".

But if you make selection in the list box, all expression values except for the selected dimension value will be 0. Is it OK or not? If not, and you want to ignore selection completely, the painless solution is to use alternate set. That is, the list box and the chart will be completely mutually independent.

Regards,

Michael

Re: Re: Re: Ignore Selections

tchovanec — Tue, 29 Oct 2013 15:01:22 GMT

Having all the other values be 0 is not what I would be expecting. So I choose PCR as the dept. abbr I would expect to see 1 for PCR and 2 for WHCY still.

Re: Re: Re: Ignore Selections

Anonymous — Tue, 29 Oct 2013 15:08:19 GMT

That's what I suspected.

What about the alternate set? Is it acceptable for you?

Edit: It is "alternate state", not "alternate set"... Example attached.

Re: Re: Re: Ignore Selections

tchovanec — Tue, 29 Oct 2013 15:12:59 GMT

If it achieves the behavior I am expecting that would be great. That chart should change anytime another dimension is selected such as date, gender, etc. The only dimension that should not affect this chart is the island_dept_abbr selection. It should always show all the depts. I have never done alternate states, would you be willing to give me an example? Thanks again.

Re: Re: Re: Ignore Selections

tchovanec — Tue, 29 Oct 2013 15:16:31 GMT

I have looked at the example you provided and every time I select PCR in the list box, the straight table still shows only PCR and not WHCY. I would expect the table to also show WHCY.

Re: Re: Re: Ignore Selections

Anonymous — Tue, 29 Oct 2013 15:24:12 GMT

Hmm... What is your QV version? Alternate set didn't exist before QV11.

Re: Re: Re: Ignore Selections

tchovanec — Tue, 29 Oct 2013 15:30:05 GMT

So I figured out how to do this on our server. My local copy was still 10.0, I am upgrading now. The issue I see with alternate states is that I still need the chart to change if any other dimension is selected. Right now the alternate state only changes when the alternate list box is used. Does that make sense?

Re: Ignore Selections

Anonymous — Tue, 29 Oct 2013 15:49:18 GMT

This is what the alternate states for. In your case, use that list box in an alternate state, and all other filters and chart itself in another (inherited in my example).

Take a look at the examples which come with QV installation, they are quite good.

Re: Ignore Selections

tchovanec — Tue, 29 Oct 2013 16:03:36 GMT

So the alternate states kind of work. In your example it definitely gives me what I expect, but now if I add in another dimension such as gender, race, etc, I would expect the numbers in the straight table to change, but they don't because either they are associated with the alternate state or if they are using the inherited state none of my other charts change when changing the dept in the list box.

Re: Ignore Selections

Anonymous — Tue, 29 Oct 2013 16:39:38 GMT

This is more or less correct.

You have to choose what to keep in the inherited state, and what in the alternate states. If only this list box should not affect this chart, it makes sense to keep this chart and all other filters (gender, race, etc.) in the inherited state, and only this particular list box in an alternate state.

You still can enforce the selections in an alternate state to affect objects in another state - see the example from QlikTech if you need this.

Regards,

Michael

Re: Ignore Selections

tchovanec — Tue, 29 Oct 2013 17:18:59 GMT

Michael,

I did some searching and I think I am really close. I read some more about alternate states and using implicit field value definitions. I am running into one issue. I the expression for the table I have altered it to be

(AGGR(IF(FirstSortedValue(
{<
ADMIT_DT ={">= $(=NUM(ADDMONTHS(MONTHSTART(MAX(ISLAND_CALENDAR.DATE)),-12))) <=$(=MAX(ISLAND_CALENDAR.DATE))"},
FLG_Z_PATIENTS = {0},
ISLAND_DEPT.DEPT_ABBR = [Alt1]::ISLAND_DEPT.DEPT_ABBR
>}
DISTINCT DEPT_ABBR,-ADMIT_DTTM)= ISLAND_DEPT.DEPT_ABBR,1,0),MRN,ISLAND_DEPT.DEPT_ABBR))

It isn't working because of the AGGR function that is still aggregating over the non-alternate state dept. abbr field. How can I get around this? I really appreciate all of the help. I have learned a ton today.

Re: Ignore Selections

Anonymous — Wed, 30 Oct 2013 14:01:27 GMT

Thomas,

If the chart is in the inherited state, and you want it to be independent from the dept (ISLAND_DEPT.DEPT_ABBR in Alt1 state) selection, you should not use this:
ISLAND_DEPT.DEPT_ABBR = [Alt1]::ISLAND_DEPT.DEPT_ABBR.
This is exactly what creates dependency between objects in the different states which you want to avoid, I believe.
I thought my attachment worked exactly the way you wanted. If not, need clarification. Or, is it a different chart this time?

As for the AGGR, I can't tell if it's possible to use alternate state with it. Try to post it as a new question, very likely there is a solution that I'm not aware of.

Additional, info, not exactly related, but maybe useful - one more way of working with alternate states, especially when you are comfortable with set analysis. In addition to {1} and {$}, you can use {Alt1} - selection in state. For example:
sum({<Field1 = {'ABC'}> * Alt1<Field2={'XYZ'}>} Amount)
Returns sum of Amount with conditions Field1 = 'ABC' in the state of the current object, and Field2='XYZ' in the alternate state Alt1. It gives you additional flexibility.

Regards,
Michael