topic Re: Number formatting in Partial Sums in QlikView

Number formatting in Partial Sums

Thu, 06 Jun 2013 06:39:20 GMT

Hi, I saw a webpage teaching how to customize Qlikview Subtotal. However, I had a problem changing the number format. Let's look at this example:

There are 12 players in the game. Some players earn 1-point and some earn 0.5 point.

And in each sub-total, I want to calculate the total no. of players and total points the players earned.

Let's say I want the subtotal in country level, "No. of Players in Country" and "Total Points in Country" (as shown on the graph above)

To do this,

First, I created a dimension called "No. of Players in Country"

Then, I checked the Partial Sums and rename as "Total Points in Country"

Finally, in the Expression, to make a correct calculation of "Total Points in Country", I use:

if(Dimensionality()=1, count({<PlayerType={'1-Point'}>} ID)+ count({<PlayerType={'0.5-Point'}>} ID)/2, count(ID))

Then, the question comes, I want the whole row of "Total Points in Country" presented as 1 decimal point place. e.g. 0 --> 0.0, 1-->1.0

When I tried:

if(Dimensionality()=1, num(count({<PlayerType={'1-Point'}>} ID)+ count({<PlayerType={'0.5-Point'}>} ID)/2,'###0.0'), count(ID))

it doesn't work. (See graph shown below)

Hope someone could help. Thanks.

Re: Number formatting in Partial Sums

Siva_Sankar — Thu, 06 Jun 2013 07:23:49 GMT

In the presentation tab, under number format settings, click 'Fixed To' 1 decimals. Regards, Sivasankar

Re: Number formatting in Partial Sums

Thu, 06 Jun 2013 07:27:32 GMT

Thanks for reply but I don't understand. Let me explain by graph.

The number format in red is what I want to show in the pivot table.

Re: Number formatting in Partial Sums

Thu, 06 Jun 2013 07:37:50 GMT

I can't find the number format settings in presentation tab.

Re: Number formatting in Partial Sums

Tue, 11 Jun 2013 02:59:20 GMT

I tired many combinaions of code in set analysis these days,

I found that dimensionality() works really well on calculations, but totally messed up on the number formatting.

Re: Number formatting in Partial Sums

amars — Tue, 11 Jun 2013 03:29:07 GMT

Change it in Expressions Text Color Settings

Thanks

Re: Number formatting in Partial Sums

Tue, 11 Jun 2013 03:42:03 GMT

Thanks for answering.

Do you know how to change the partial sum in the number format 0.0 instead of 0?

In general, I can type num(player, '###0.0') to change the number format.

However, I can't apply the formula on subtotal,

and I tried to use dimensionality(), but the number format of some data is wrong.

Re: Number formatting in Partial Sums

amars — Tue, 11 Jun 2013 04:29:42 GMT

please check the attachec file.

if(Dimensionality()=1, Num(count({<PlayerType={'1-Point'}>} ID)+ count({<PlayerType={'0.5-Point'}>} ID)/2,'###0.0'), count(ID))

Re: Number formatting in Partial Sums

Tue, 11 Jun 2013 04:43:05 GMT

I tried this formula, but it gives me the same result.

Re: Number formatting in Partial Sums

amars — Tue, 11 Jun 2013 05:52:30 GMT

check the attached file above, it is working .

Re: Number formatting in Partial Sums

Tue, 11 Jun 2013 07:43:32 GMT

Thanks again amars.

I can't open your attached file above because I'm still using Personal Edition.

Can you do me a favor that print a screen to show the location that you put the formula?

I tried to put this formula in the Expression, and it doesn't work.

Maybe I put in a wrong position.

Re: Number formatting in Partial Sums

amars — Tue, 11 Jun 2013 08:34:46 GMT

Re: Number formatting in Partial Sums

Tue, 11 Jun 2013 08:43:34 GMT

Don't understand why my Qlikview can't do it with the same input.

Anyways, thank you very much!