Urgently need some help.

[#1]

Hello everyone, I would appreciate this if anyone could help me out with this question.

If I have hospitals as a field and three months of quantity preceding this column, i.e. 201307, 201308, 201309. I want to sort out the hospitals that have any two out of the three months >1 quantity. How can I do this? In Excel, this would have been countif(A2:A3, >1), followed by filter/sorting the table with >1. How can I achieve this in Qlikview?

Thanks,

Charles

[jagan]

Hi,

Please find attached file for solution hope it helps you.

Regards,

Jagan.

[sujeetsingh]

Hello see the sample i have pasted

[#4]

Dear Charles Liu,

Please try this.

Thank you.

[#5]

Thank you all for your quick responses. But I might have phrased the question wrong that I wasn't getting the results I wanted.

Please see the table below first:

	Quantity	Quantity	Quantity
Hospital	201307	201308	201309
Christie Hospital	2	0	3
St. John's Hospital	1	2	2
Philly Hospital	0	0	2
Mount Sinai Hospital	1	3	1

Criteria
MET (>1 quantity in at least 2 out of 3 months)
UNMET

What I want to achieve is when selecting "MET" in the field "Criteria", the table above would only show qualifying hospitals (like table below):

	Quantity	Quantity	Quantity
Hospital	201307	201308	201309
Christie Hospital	2	0	3
St. John's Hospital	1	2	2

I hope this clarifies my original question and I hope this would not be too much of a challenge for you qlikview gurus.

Thanks

Charles

[#6]

Hi,

    Check this attach file and let me know.

I hope this helps you.

Regards,

Ashutosh

[jagan]

Hi,

Try this in script,

Data:

LOAD
Hospital, M201307, M201308, M201309
WHERE Flag = 1;
LOAD
*,
If(Rangesum(If(M201307 > 1, 1), If(M201308 > 1, 1), If(M201309 > 1, 1)) >=2, 1) AS Flag
INLINE [
Hospital, M201307, M201308, M201309
Christie Hospital, 2, 0, 3
St. John's Hospital, 1, 2, 2
Philly Hospital, 0, 0, 2
Mount Sinai Hospital, 1, 3, 1];

Regards,

Jagan.

[#8]

hi ,

use the below expression in pivot .. it works for me which returns 2 hospital as per ur result.

if(aggr(Nodistinct sum(if(quantity>=2,1,0)),Hospital)>=2,sum(Quantity))

Regards

kumar.R

[#9]

Hello Charles,

Please find the attached document and the excel file. I have also added the filter.

Thanks,

Angad