Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prahlad_infy
Partner - Creator II
Partner - Creator II

Please helping me for one set expression .

Hello Sirs ,

how to be the expression to find count of previliged traders (EMP) which are having zero bidding_id during period selected .

with no change in present only  data model structure .


Thank you .

1 Solution

Accepted Solutions
sunny_talwar

Try this

=Count({$<Trader_Name = {"=(Count({<Date_of_Bidding={[>=$(=Date(vStart_Date, 'DD-MMM-YYYY'))<=$(=Date(vEnd_Date, 'DD-MMM-YYYY'))]}>} Bidding_ID) + Sum({1} 0)) = 0"}, Marker = {"Previlaged"}>} DISTINCT Trader_Name)

View solution in original post

9 Replies
sunny_talwar

What is the expected output based on the data within the sample and selections made?

prahlad_infy
Partner - Creator II
Partner - Creator II
Author

Thank you sir for replying

My result should be

zeros.png


PIC FOR REFERENCE

zeros2.png


if you see in reference pic i am getting my results by doing filter in excel with two expression ,

i want a single set expression that should give me count in TEXT BOX  ,
all those trader which are privileged but have no complete bidding id in the period selected in calender box ..

sunny_talwar

Try this

=Count({$<Trader_Name = {"=(Count({<Date_of_Bidding={[>=$(=Date(vStart_Date, 'DD-MMM-YYYY'))<=$(=Date(vEnd_Date, 'DD-MMM-YYYY'))]}>} Bidding_ID) + Sum({1} 0)) = 0"}, Marker = {"Previlaged"}>} DISTINCT Trader_Name)

prahlad_infy
Partner - Creator II
Partner - Creator II
Author

I think its not working properly . try range - 1/july/2018 to 12/nov/2018

zeros not working.png

sunny_talwar

Yes, because between those two dates, all the Traders seem to have bid for at least 1 bid. Wouldn't that cause you to see 0?

prahlad_infy
Partner - Creator II
Partner - Creator II
Author

Sorry sir i think , i made mistake , your solution is correct .. let me double check .

sergio0592
Specialist III
Specialist III

Sunny, can you explain why you use [ instead " or ' ?

Date_of_Bidding={[>=$(=Date(vStart_Date, 'DD-MMM-YYYY'))<=$(=Date(vEnd_Date, 'DD-MMM-YYYY'))]}>} Bidding_ID)


Thanks

sunny_talwar

Since this whole thing is part of a bigger set analysis... I used [] instead of escape sequence

=Count({$<Trader_Name = {"=(Count({<Date_of_Bidding={[>=$(=Date(vStart_Date, 'DD-MMM-YYYY'))<=$(=Date(vEnd_Date, 'DD-MMM-YYYY'))]}>} Bidding_ID) + Sum({1} 0)) = 0"}, Marker = {"Previlaged"}>} DISTINCT Trader_Name)

sergio0592
Specialist III
Specialist III

Ok, understood. Thanks!