Set Analysis Using 'Or' Condition and Distinct Count

[kristen_ludlow]

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

[sunny_talwar]

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

[kristen_ludlow]

Hey Sunny,

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

[sunny_talwar]

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

[sunny_talwar]

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

[kristen_ludlow]

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!

[kristen_ludlow]

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

[sunny_talwar]

But this hasn't worked?

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

[kristen_ludlow]

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]

[sunny_talwar]

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?