topic Re: Set Analysis Using 'Or' Condition and Distinct Count in App Development

Set Analysis Using 'Or' Condition and Distinct Count

kristen_ludlow — Wed, 22 Dec 2021 15:42:54 GMT

I am working to create a percentage of total PO Lines based on two different flags. If at least one of the Flag is equal to 'Y', I want to count that line. The count is also dependent on a date variable for the current month and year. When I conduct the below set analysis, the formula does not work. I have tested the variable and know that it works. I have used the same formula for a sum of total spend instead of a count and the formula worked. Can anyone offer some support as to where I am going wrong or offer a different solution? Thank you so much!

Field set in script:

Date([PO Date],'MMM-YYYY') as MonthYear,

Variable:

vCurMonthYear=DATE(max([PO Date]),'MMM-YYYY')

Set Analysis:

count({$<[Cntrct Flg]={'Y'}, MonthYear={'$(=vCurMonthYear)'}>+<[Active]={'Y'}, MonthYear={'$(=vCurMonthYear)'}>}distinct[PO Line])/count({$<MonthYear={'$(=vCurMonthYear)'}>}distinct[PO Line])

Re: Set Analysis Using 'Or' Condition and Distinct Count

sunny_talwar — Fri, 29 Jun 2018 02:55:29 GMT

Would you be able to share a sample so that we can check it out?

Re: Set Analysis Using 'Or' Condition and Distinct Count

kristen_ludlow — Fri, 29 Jun 2018 14:48:42 GMT

Hey Sunny,

Let me know if the attached sample will work. Thank you for your help!

Re: Set Analysis Using 'Or' Condition and Distinct Count

sunny_talwar — Fri, 29 Jun 2018 14:58:09 GMT

This should work, the only thing which will be helpful to know would be the expected output based on the sample provided?

Re: Set Analysis Using 'Or' Condition and Distinct Count

sunny_talwar — Fri, 29 Jun 2018 15:05:11 GMT

Are you hoping to see 1438? May be try to change the variable to this

vCurMonthYear = Date(MonthStart(Max([PO Date])),'MMM-YYYY');

Where MonthYear field is created in the script like this

Date(MonthStart([PO Date]), 'MMM-YYYY') as MonthYear

Re: Set Analysis Using 'Or' Condition and Distinct Count

kristen_ludlow — Fri, 29 Jun 2018 17:26:53 GMT

Sunny,

yes, the PO Line count with the "Y" flag would be 1438. Ultimately, I am hoping to obtain a percentage value of distinct PO Lines that have either a Y flag in the "Cntrct Flg" or "Active" Column out of the total distinct PO Lines. I have attached a sample of the formula I would write in Excel to achieve this with a combination of a Pivot Table to show the percentage value I am looking for.

I will try to update the variable based on your suggestion.

Thank you again for all of your help!

Re: Set Analysis Using 'Or' Condition and Distinct Count

kristen_ludlow — Fri, 29 Jun 2018 18:28:41 GMT

Sunny,

I updated the variable and the script with your suggestions. As separate formulas the below both work for me but not combined together. How I want to combine them to count the distinct PO Lines that have a "Y" in either the "Cntrct Flg" Field or the "Active" Field divded into the distinct count of PO Lines?

count({$<[Active]={'Y'}, MonthYear={'$(=vCurMonthYear)'}>} distinct [PO Line])/count({$<MonthYear={'$(=vCurMonthYear)'}>} distinct [PO Line])

count({$<[Cntrct Flg]={'Y'}, MonthYear={'$(=vCurMonthYear)'}>} distinct [PO Line])/count({$<MonthYear={'$(=vCurMonthYear)'}>} distinct [PO Line])

Re: Set Analysis Using 'Or' Condition and Distinct Count

sunny_talwar — Sat, 30 Jun 2018 11:44:13 GMT

But this hasn't worked?

Count({$<[Active] = {'Y'}, MonthYear = {'$(=vCurMonthYear)'}>+<[Cntrct Flg] = {'Y'}, MonthYear = {'$(=vCurMonthYear)'}>} DISTINCT [PO Line])/Count({$<MonthYear = {'$(=vCurMonthYear)'}>} DISTINCT [PO Line])

Re: Set Analysis Using 'Or' Condition and Distinct Count

kristen_ludlow — Mon, 02 Jul 2018 15:38:55 GMT

The above formula provides a value of 0.0%. A similar formula using Sum and Total Spend provide me with a percentage value.

sum({$<[Cntrct Flg]={'Y'},MonthYear={'$(=vCurMonthYear)'}>+<[Active]={'Y'},MonthYear='$(=vCurMonthYear)'}>}[Total Spend])/sum({$<MonthYear={'$(=vCurMonthYear)'}>}[Total Spend])

In the Script, I mapped in the field "Active" from the Contracts Table to the PO Table. There are only about 90 Suppliers in the Contract Table so they do not all correspond to the Suppliers in the PO Table. I was having issues with formulas so I mapped in the "Active" field to match up with the Supplier name. Do you think this could be a reason the percent value does not work with count?

Example of Tables

Contracts:

LOAD Owner

[Supplier ID],

[Supplier Name],

[Contract Category],

Discount Payment Terms],

[Active] as [PCF]

Contracts_Map:

mapping LOAD

[Supplier Name],

[Active]

PO:

LOAD Year,

Quarter,

month(Month) as Month,

[Client Name],

[PO Date],

year([PO Date]) as Yearcreated,

QuarterName([PO Date]) as Quartercreated,

Month([PO Date]) as Monthcreated,

Date(MonthStart([PO Date]), 'MMM-YYYY') as MonthYear,

[PO No.],

[PO No.]&'_'&[PO Line] as [PO Line],

[PO Descr],

[Supplier Name],

Quantity,

[UOM],

if(len([Cntrct Flg])=0,'N',[Cntrct Flg]) as [Cntrct Flg],

[Total Spend],

applymap('Contracts_Map',[Supplier Name],'N') as [Active]

Re: Set Analysis Using 'Or' Condition and Distinct Count

sunny_talwar — Wed, 04 Jul 2018 23:12:21 GMT

So, the same set analysis with Total Spend works, but PO Line doesn't work? That is sort of very strange... is there any way you can share a sample to test this out?

Re: Set Analysis Using 'Or' Condition and Distinct Count

kristen_ludlow — Fri, 06 Jul 2018 20:09:16 GMT

Yes, the total spend set analysis works. I dont' think I will be able to share a sample. It is possible there could be missing data for the PO Line. If there are null values could that cause any issues? The QlikSense version I am using is November 2017, do you think there could be possible bugs in the system? I am giong to try to recreate the same App on Qlik Desktop to see if there could be any bugs with the eneterprise version.