topic Re: Need to set different number forrmating for inline table in QlikView

Need to set different number forrmating for inline table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am new to QlikView. In my project, I created an inline table with Actual, Thousands and Millions value and used it for calculation in my expressions.

Now I need to set different number format(i.e) Without decimal places for Actuals and with 2 decimal places for Millions and Thousands.

How to set this number format in expressions for the inline values?

Can anyone help me with this?

Re: Need to set different number forrmating for inline table

prieper — Tue, 16 May 2017 10:51:17 GMT

would you please share an example?

Re: Need to set different number forrmating for inline table

qlikview979 — Tue, 16 May 2017 10:57:39 GMT

Hi,

We can set the number format in Number tab.

would you Please share the sample app.

Re: Need to set different number forrmating for inline table

vinieme12 — Tue, 16 May 2017 11:07:31 GMT

please post sample app

Re: Need to set different number forrmating for inline table

Peter_Cammaert — Tue, 16 May 2017 11:23:19 GMT

Yes we can help you with this. Please post an example of your INLINE table.

Re: Need to set different number forrmating for inline table

Anonymous — Tue, 16 May 2017 11:29:06 GMT

Factor_Table:

LOAD * INLINE

[

Factor_Id, _Factor, Factor_Value

1, Millions, 1

2, Thousands, 1000

3, Actuals, 1000000

];

In dashboard created a list box with "Actuals, Millions, Thousands"

and using the Inline table value in my expression.

example: sum(Sales)*$(=Factor_Value)

In this expression, I need to set the number format as #,##0 when the user clicks Actuals in the list box and need to set the number format as #,##0.0 when the user clicks Millions and Thousands list box.

how to set this. I tried using If statement in the expression. but no change. can you help with this?

Re: Need to set different number forrmating for inline table

Anil_Babu_Samineni — Tue, 16 May 2017 11:32:37 GMT

May be this?

=If(sum(Sales)*$(=Factor_Value)/1000000000 > 1,Num(sum(Sales)*$(=Factor_Value)/1000000000,'$#,###B'), Num(sum(Sales)*$(=Factor_Value)/1000000,'$#,###M'))

Re: Need to set different number forrmating for inline table

prieper — Tue, 16 May 2017 11:51:43 GMT

something is weird in your formula,

would expect to have the formula to read sum(Sales) / $(=Factor_Value).

Further the $(=) is not required.

So your formula may read

SUM(Sales / Factor_Value).

If you wish to keep the formula, you may change the inline-table to read for e.g. Millions then 1/1000000.

Formatting might be done in the settings, else you may also store the formatting assigned to the _Factor in the inline-table.

Formulas then to read

NUM(SUM(Sales/Factor_Value), _MyFormat)

Inline then may look:

Format:

LOAD * INLINE [FormatName, FormatFactor, Format

Actual, 1, "#,##0.00"

Thousand, 1000, "#,##0"];

HTH Peter

edit: format-Table inserted

Re: Need to set different number forrmating for inline table

Anonymous — Tue, 16 May 2017 11:53:27 GMT

No, it didn't work

Re: Need to set different number forrmating for inline table

Anonymous — Tue, 16 May 2017 12:28:40 GMT

No changes happened.

Is it possible to set different number formats for each values in a Inline table?

Re: Need to set different number forrmating for inline table

Anil_Babu_Samineni — Tue, 16 May 2017 12:29:53 GMT

Then seems, You must need Mapping table with the existing and new tables by help of ApplyMap() function

Re: Need to set different number forrmating for inline table

prieper — Tue, 16 May 2017 12:42:20 GMT

pfa

Re: Need to set different number forrmating for inline table

sunilkumarqv — Wed, 17 May 2017 04:43:33 GMT

Hi Kiran,

Please try this

=If(GetselectedCount(_Factor)=0,Pick(Match(_Factor,'Millions','Thousands',Actuals')

NUM(SUM(Sales/Factor_Value), '#,##0.00;'),

NUM(SUM(Sales/Factor_Value), '#,##0.0 ;'),

NUM(SUM(Sales/Factor_Value), '#,##0;')),

NUM(SUM(Sales/Factor_Value), '#,##0;'))