Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

paulacalvalis
New Contributor III

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

Re: Set analysis with comparative field.

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

8 Replies
agilos_mla
Contributor III

Re: Set analysis with comparative field.

‌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

Re: Set analysis with comparative field.

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

paulacalvalis
New Contributor III

Re: Set analysis with comparative field.

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

So it's wrong.

Cheers from Spain!

antoniotiman
Honored Contributor III

Re: Set analysis with comparative field.

Hi Paula,

Try

Count(If(IssueDate=ReceptionDate,OrderID))

Regards,

Antonio

paulacalvalis
New Contributor III

Re: Set analysis with comparative field.

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
Honored Contributor III

Re: Set analysis with comparative field.

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

paulacalvalis
New Contributor III

Re: Set analysis with comparative field.

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

Re: Set analysis with comparative field.

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

Community Browser