Numeric Money Formatting Oddity?

[#1]

Dear All,

New to QlikView, and seem to be doing something dumb. Basically, displaying a chart as a table:

Item     Year     Quarter     Forecast     Actual     Actual-Forecast

Fred     2013     Q1          10               £12.00     £2.00

Fred     2013     Q2          12               £10.00     -£2.00

etc ...

Item, Year and Quarter are set up as dimensions, the Forecast, Actuals and difference are expressions.

The example gives a clue to the problem: The actuals, and the differences between actual and forecast, are being displayed by setting Money on the Numeric tab of the chart object, and are displayed as Money according to the system settings. But Forecast will not honour the setting - it resolutely stays as an unformatted number, even though the property setting is clearly set for Money. I can't identify any obvious differences in any of the field settings.

The data is being loaded from an SQL Server database, into a couple of largeish Forecast and Actual tables (which have a load of info not relevant to this issue). I'm then aggregating via this script chunk:

LatestForecasts:  //Pulls out the latest forecast via the index

LOAD "Item"

    ,"Year"

                       ,"Qtr"

                       ,firstsortedvalue("Value", -"Index") AS "Forecast"

RESIDENT Forecasts

GROUP BY "Item","Year","Qtr" ;

LatestActuals:  //Aggregates actuals as they are split up

LOAD "Item"

    ,"Year"

                       ,"Qtr"

                       ,SUM("Value") AS "Actual"

RESIDENT Actuals

GROUP BY "Item","Year","Qtr" ;

Basically forecasting is doen at the item/year/quarter level, but actuals are actually stored against sub-items, so need to be summed. On the other hand, whilst there is only one actual for any given sub-item, there can be multiple forecasts, and here we just want the latest.

The table is produced from these fields, along with the calculated expression for the difference between actuals and forecasts. But the formatting will not work for the Forecast field (in fact, none of the format settings seem to be honoured for this field). Anyone have any suggestion as to reason?

Thanks,

Prune.

[Gysbert_Wassenaar]

Hard to say without being able to look at the actual data. You could try using the num function to specify a format: num( myexpression , '£ 0.00'). Or maybe add a little fraction to it myexpression + 0.000000000001. Or perhaps you need to use the num# function in the script in (the unlikely) case you get strings instead of actual numbers in the Forecast field.

[#3 · Accepted solution]

Thanks, but I finally sorted it by ripping out all the expressions and then putting them back in. So that's solved it, although it's not clear why the problem happened in the first place!

-- Prune