Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I am trying to calculate the price for PO.
so my formula will be Net price*exchange rate
I have table for exchange rate in below format
I have crated association between calendar month and PO generated month .
I want to create an expression(not in script) if we have a PO for the month of May/2016 and exchange rate is not available in exchange rate table, it should pick exchange rate of last available month . In this case it is Apr/2016.
I tried creating variable with firstsortedvalue but when I tried inserting that variable in other sum function it showing nested aggregation not allowed.
if(wildmatch(CADtoUSDExchangeRate,num(CADtoUSDExchangeRate))=1,CADtoUSDExchangeRate,FirstSortedValue(CADtoUSDExchangeRate,-CalendarMonth)))
can anyone help me with this?
Hi, it would be better if you solve this in script, I read that you want an expression but don't know why, the steps would be:
- Create a calendar with all year-month from the min value loaded to the max. ie:
LOAD
Date(AddMonths(MinDate, IterNo()-1),'MMM/YYYY') as YearMonth
While AddMonths(MinDate, IterNo()-1) <= MaxDate
;
LOAD
MonthStart(Min(FieldValue('DateField', RecNo()))) as MinDate,
MonthStart(Max(FieldValue('DateField', RecNo()))) as MaxDate
AutoGenerate FieldValueCount('DateField');
- Join the CADtoUSDExchangeRate with this table to populate infomred months.
- LOAD resident from this table, sort by the oldest date and use Peek() to reteive the last value when a YearMonth doesn't has any value
LOAD
YearMonth,
If(IsNull(CADtoUSDExchangeRate), Peek(CADtoUSDExchangeRate), CADtoUSDExchangeRate) as CADtoUSDExchangeRate
Resident table
Order By YearMonth;
Hi Ruben
Thanks for the response I am using Data Manager and not able to work with script.
Is there any way to do in variable or Master measure?