- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]