Set Analysis Logic

[nihhalmca]

Hi All -

Please assist to write set analysis logic. Attached document for detail explanation and sample data.

Thank you.

[sidhiq91]

@nihhalmca Instead of Set Analysis, we can get this done in the script Editor itself something like below:

Temp:
LOAD

"Order Number",
"Actual Date",
"Target Date",
Status
FROM [lib://Qlik Community Practice/Set Analysis Logic.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1)

where "Actual Date"="Target Date" and not isnull(Status);

inner join (Temp)
Temp1:
Load "Order Number"
where total_count=1;

Load count("Order Number") as total_count,
"Order Number"
Resident Temp
group by "Order Number";

Exit Script;

In the front end for the KPI: Count(distinct status)

[nihhalmca]

No sidhiq, actual scenario those columns coming from different tables. Need to perform more transformations so trying to achieve at front end with set analysis.

[sidhiq91]

@nihhalmca  then in that case below mentioned Set expression will resolve the issue.

You can use it for KPI's and Detail table

count({<[Order Number]={"=count([Order Number])=1"}, [Actual Date]=P([Target Date]), Status={'*'}>}Status)

[sidhiq91]

@nihhalmca  You can use below expression as a measure in the straight table:

=only({<[Actual Date]=P([Target Date]), [Order Number]={"=count([Order Number])=1"}, Status={'*'}>}[Order Number])

This should resolve the issue.

[nihhalmca]

@sidhiq91  Thanks for response.

Null is coming actual have to avoid it (removed single quotations for wildcard (*) in expression with quotations not working).

[sidhiq91 · Accepted solution]

@nihhalmca  It perfectly worked fine for me. Please see the screen shot below. Or provide me some more real time data.

[nihhalmca]

@sidhiq91  Anything wrong in expression ? Please see the screenshots. if I remove single quotation for * it include nulls. My version is Qlik Sense April 2020.

With Single Quotations:

Without Single Quotations:

Sample Data I used:

load * inline [
OrderNumber, ActualDate, TargetDate, Status
A123, 25-07-2022, 25-07-2022, Delivered,
B123, 26-07-2022, 25-07-2022, Delivered,
C123, 21-07-2022, 21-07-2022, Delivered,
D123, 22-07-2022, 22-07-2022, ,
E123, 29-07-2022, 29-07-2022, Delivered,
E123, 29-07-2022, 29-07-2022, Returned ,
];

[sidhiq91]

@nihhalmca I am exactly not sure what is happening. I would rather suggest you to remove that part of the code in the set Analysis i.e.  Status={'*'}, because the it is going only non null values. so you should get the right result.

For the Details table, in the dimension section uncheck the include null values. Hope that should resolve your issues.

[nihhalmca]

@sidhiq91  I used Status={'Delivered'}. Thanks for your help.