Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Urgently need some help.

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

8 Replies
jagan
Partner - Champion III
Partner - Champion III

Hi,

Please find attached file for solution hope it helps you.

Regards,

Jagan.

sujeetsingh
Master III
Master III

Hello see the sample i have pasted

Not applicable
Author

Dear Charles Liu,

Please try this.

Thank you.

Not applicable
Author

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:

QuantityQuantityQuantity
Hospital201307201308201309
Christie Hospital203
St. John's Hospital122
Philly Hospital002
Mount Sinai Hospital131
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):

QuantityQuantityQuantity
Hospital201307201308201309
Christie Hospital203
St. John's Hospital122

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

Not applicable
Author

Hi,

    Check this attach file and let me know.

I hope this helps you.

Regards,

Ashutosh

jagan
Partner - Champion III
Partner - Champion III

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.

Not applicable
Author

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

1.png


Regards

kumar.R

Not applicable
Author

Hello Charles,

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

Thanks,

Angad