topic Re: Apply different number format in QlikView

Apply different number format

Fri, 31 Oct 2014 20:06:50 GMT

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.

Re: Apply different number format

JonnyPoole — Fri, 31 Oct 2014 20:13:40 GMT

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

)

Re: Apply different number format

Fri, 31 Oct 2014 20:23:07 GMT

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'.

Re: Apply different number format

JonnyPoole — Fri, 31 Oct 2014 20:37:46 GMT

if( wildmatch(MeasureName,'* Qty' ) ,

num( Sum ({<MonthName=,MonthID={">=1<=$(=($(vMonthID)))"}>}[2014 Actual])/1000, ' #,##0'),

num( Sum ({<MonthName=,MonthID={">=1<=$(=($(vMonthID)))"}>}[2014 Actual])/1000,'$#,##0')

)

Re: Re: Apply different number format

Fri, 31 Oct 2014 22:28:48 GMT

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

Re: Apply different number format

JonnyPoole — Fri, 31 Oct 2014 23:53:22 GMT

Can you share the QVW ? I feel like there are a few things going on in the background.