Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
In the presentation tab, under number format settings, click 'Fixed To' 1 decimals. Regards, Sivasankar
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.
I can't find the number format settings in presentation tab.
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.
Change it in Expressions Text Color Settings
Thanks
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.
Hi
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))
I tried this formula, but it gives me the same result.
check the attached file above, it is working .