Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I am creating a Pivot Table field using following expression:
sum({<Data_Period={'YTD'},Formatted_Date={'$(vYTDNew)'}>}Chargeable)/sum({<Data_Period={'YTD'},Formatted_Date={'$(vYTDNew)'}>}Standard_Avail)
and the variable vYTDNew definition is:
=Date(IF($(vLastMonth)<=$(vMaxYTD),$(vLastMonth),$(vMaxYTD)),'dd/MM/yyyy')
The above expression is not working for me. However, if I replace nested variables with their actual definitions like below then it works fine:
=Date(IF(MonthStart(Date(MonthStart(Only(BaseData_Date))-1,'dd/MM/yyyy')) <=Max({<Data_Period={'YTD'}>} Date),MonthStart(Date(MonthStart(Only(BaseData_Date))-1,'dd/MM/yyyy')) ,Max({<Data_Period={'YTD'}>} Date)),'dd/MM/yyyy')
IO tested the variable vYTDNew using text object and it is returning the expected value.
Not sure why it does not work in the expression that I am using for creating pivot table field. Pelase help.
Thanks in advance.
Try:
sum({<Data_Period={'YTD'},Formatted_Date={$(=vYTDNew)}>}Chargeable)/sum({<Data_Period={'YTD'},Formatted_Date={$(=vYTDNew)}>}Standard_Avail)
=Date(IF($(=vLastMonth)<=$(=vMaxYTD),$(=vLastMonth),$(=vMaxYTD)),'dd/MM/yyyy')
thanks