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: 
Anonymous
Not applicable

Set analysis with comparative field.

Hello,

I'm doing a set analysis in QS to know the number of Orders.

One requierement is that i want to known the number of Orders when the Date of Issue is equal to the Reception Date.

IssueDate and ReceptionDate are fields of my DataBase.

I have one this set analysis:

Count({<ReceptionDate={"=date(IssueDate)"}>} OrderID)

But the result is wrong.

Anyone can help me?

Thanks a lot.

Regards,

Paula.

1 Solution

Accepted Solutions
marcus_sommer

Try something like this:

Count({<ReceptionDate={"$(=date(IssueDate))"}>} OrderID)

     or

Count({<OrderID={"=ReceptionDate=date(IssueDate)"}>} OrderID)

whereby both date-fields must have the same date-format.

- Marcus

View solution in original post

8 Replies
agilos_mla
Partner - Creator III
Partner - Creator III

‌hi,

you need a third field because the set analysis field cannot be in the tedt condition so use :

count({<orderid ={"=(receptiondate = issuedate)"}>} orderid)

cheers from Belgium

michael

marcus_sommer

Try something like this:

Count({<ReceptionDate={"$(=date(IssueDate))"}>} OrderID)

     or

Count({<OrderID={"=ReceptionDate=date(IssueDate)"}>} OrderID)

whereby both date-fields must have the same date-format.

- Marcus

Anonymous
Not applicable
Author

Thanks for your answer Michael, but this expression, returns error and shut down the computer...

So it's wrong.

Cheers from Spain!

antoniotiman
Master III
Master III

Hi Paula,

Try

Count(If(IssueDate=ReceptionDate,OrderID))

Regards,

Antonio

Anonymous
Not applicable
Author

Hi Antonio,

i need to do the set analysis because i have a lot of restrictions, and one of this restrictions is thar ReceptionDate, must be equal to the Issue Date.

Thanks a lot.

Paula.

antoniotiman
Master III
Master III

Paula,

You remember that SetAnalysis is per chart and not per Row.

You can also combine SetAnalysis and If Conditions, like

Count{<City={'Bari','Milano'}>} If(IssueDate=ReceptionDate,OrderID))

Regards,

Antonio

Anonymous
Not applicable
Author

Ok Marcus, it seems to work.

The problem is that i have to select the ReceptionDate in a filter to receive one result.

Could i obtain the Total Orders of all dates that are equal?

When  ReceptionDate is 01/02/2016 and IssueDate is 01/02/2016 are 50 Orders,

When  ReceptionDate is 02/02/2016 and IssueDate is 02/02/2016 are 20 Orders,

When  ReceptionDate is 03/02/2016 and IssueDate is 03/02/2016 are 30 Orders.

I need to obtain 100 orders in my indicator, and with your expressions i have obtained 50 if i select 01/02/2016, 20 if i select 02/02/2016 and 30 if i select 03/02/2016.

Thanks for all!

Paula

marcus_sommer

Without knowing your datamodel it's difficult to say what to do to get a certain result. The first expression:

Count({<ReceptionDate={"$(=date(IssueDate))"}>} OrderID)

will only work for a certain selected date then $-sign expansion within the set analysis will be only evaluated once globally for the whole chart and then applied for each row.

The second expression:

Count({<OrderID={"=ReceptionDate=date(IssueDate)"}>} OrderID)

instead worked on a row-level and might work in your case but it's depending on your datamodel.

- Marcus