topic Re: Set Analysis Logic in App Development

Set Analysis Logic

nihhalmca — Wed, 03 Aug 2022 15:37:16 GMT

Hi All -

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

Thank you.

Re: Set Analysis Logic

sidhiq91 — Wed, 03 Aug 2022 17:15:34 GMT

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

Re: Set Analysis Logic

nihhalmca — Thu, 04 Aug 2022 05:13:22 GMT

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.

Re: Set Analysis Logic

sidhiq91 — Thu, 04 Aug 2022 06:03:38 GMT

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

Re: Set Analysis Logic

sidhiq91 — Thu, 04 Aug 2022 06:08:43 GMT

@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.

Re: Set Analysis Logic

nihhalmca — Thu, 04 Aug 2022 08:55:17 GMT

@sidhiq91 Thanks for response.

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

Re: Set Analysis Logic

sidhiq91 — Thu, 04 Aug 2022 08:47:02 GMT

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

Re: Set Analysis Logic

nihhalmca — Thu, 04 Aug 2022 09:18:04 GMT

@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 ,
];

Re: Set Analysis Logic

sidhiq91 — Thu, 04 Aug 2022 10:03:10 GMT

@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.

Re: Set Analysis Logic

nihhalmca — Thu, 04 Aug 2022 10:51:24 GMT

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