topic Re: Inconsistent Num() Formatting on Income Statement in QlikView

Inconsistent Num() Formatting on Income Statement

wandapec — Thu, 13 May 2021 08:58:21 GMT

I have a model displaying an Income Statement. The following expression displays the P&L and uses the Num() format of MinString(Format). Format is a field in an Excel spreadsheet for each line of the Income Statement and is R#,##0;(R#,##0), so an example would be:

Expression:

if([Exec P&L Level] = 's','',
Num(
(Sum({$}Amount))
/fabs(if(Flag=1, $(=Sum({$}SalesAmount)),if(Flag=2, $(=Sum({$}GPAmount)), 1)))
, Minstring(Format))
)

For some reason, and I can't work out the pattern, it sometimes doesn't apply the formatting. Check the Income Tax line in the image below. In the example below, I have selected YTD Jan to Apr. When I select YTD Jan to Mar or Feb, it works fine...

E.g. Jan to Apr

E.g. Jan to Mar

Any ideas? Thanks.

Re: Inconsistent Num() Formatting on Income Statement

Gysbert_Wassenaar — Thu, 13 May 2021 09:10:20 GMT

Add a text object and put Minstring(Format) in it. Then make your date selections to check if Minstring(Format) always returns what you expect.

Re: Inconsistent Num() Formatting on Income Statement

wandapec — Fri, 21 May 2021 06:40:52 GMT

Thanks Gysbert.
I eventually found the problem. If there is not transaction for the relevant GL Code in the particular month that you have selected, i.e. items are greyed out in a list box, then it doesn't know what format to use for any historical transactions showing in the chart. I will disconnect the calendar from the transaction and use a variable to reference the relevant month selected... Hopefully that will work....