Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am doing a P&L statement. The first 3 lines in the Dimension/Field is for Quantity (Kg) and the remainder is Cost/Revenue ($).
For the quantity lines I don't want to format tthe numbers with a preceding $ but everything else that is Revenue/Cost should be in the currency format.
In my expression, I tried using :
num(Sum ({<MonthName=,MonthID={">=1<=$(=($(vMonthID)))"}>}[2014 Actual])/1000,if(MeasureName='* Qty',' #,##0','$#,##0'))
This still displays every line in currency format and will not ignore for those lines which end in the text 'Qty'.
Can anyone help ?
Thanks.
Jay.
What are the values for MeasureName ? Also ensure your number tab has not designated an output format.
if(MeasureName='* Qty',
num( Sum ({<MonthName=,MonthID={">=1<=$(=($(vMonthID)))"}>}[2014 Actual])/1000, ' #,##0'),
num( Sum ({<MonthName=,MonthID={">=1<=$(=($(vMonthID)))"}>}[2014 Actual])/1000,'$#,##0')
)
Still displays all lines in currency format.
The MeasureName values for quantity are 'Reporting Qty', 'Sales Qty' and 'Base Qty'.
The number tab is set to 'Expression Default'.
if( wildmatch(MeasureName,'* Qty' ) ,
num( Sum ({<MonthName=,MonthID={">=1<=$(=($(vMonthID)))"}>}[2014 Actual])/1000, ' #,##0'),
num( Sum ({<MonthName=,MonthID={">=1<=$(=($(vMonthID)))"}>}[2014 Actual])/1000,'$#,##0')
)
Thanks Jonathan.
Unfortunately It only works for the last month and not all the months.
Its a pivot table with 2 dimensions (MeasureName and Month) and I dragged the month as columns to give me a trend report.
see attached
Can you share the QVW ? I feel like there are a few things going on in the background.