Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would like to create in a table a subtotal on a population category. And a total on the whole population. With a peculiarity on the percentages as the following table
thanks you for your help
Age:
LOAD Age,
Customer,
if(Match(slice,'70 ans and more','60-70 years','50-60 years','40-50 years','30-40 years','0-30 years'),'Know',
if(slice='Unknow','Unknow')) as Group,
if(Match(slice,'70 ans and more','60-70 years','50-60 years','40-50 years','30-40 years','0-30 years'),slice) as Know_Slice,
if(slice='Unknow',slice) as Unknow_Slice,
slice
FROM
[Age.xlsx]
(ooxml, embedded labels, table is Sheet1);
Sort:
LOAD * Inline [
Know_Slice,Sort
70 ans and more,6
60-70 years,5
50-60 years,4
40-50 years,3
30-40 years,2
0-30 years,1 ];
Dim:
LOAD * Inline [
Dim
1
2
3
4 ];
Create Straight table
Dimension:
=Pick(Dim,Know_Slice,'Under Total',Unknow_Slice,'Total')
=Pick(Dim,
Count(Customer)/Count( total Customer),
Count({<Group={'Know'}>}Customer)/Count(total Customer),
Count(Customer)/Count(total Customer),
Count(Customer)/Count(total Customer))
Sort Expression:
=Pick(Dim,Sort,8,9,10)
Hi
Can u plz share a sample app.
Regards
Pratyush
May be this?
Sum(Number)/Sum(TOTAL Number)
OR
Sum(Number)/Sum(TOTAL <Dimension1> Number) //Dimension1 is nothing but your Dimensions
hello,
A sample app
Thanks
HI
PFA the app with the calculations.
Regards
Pratyush
Thanks but i need a subtotal for these catégories
One simple way, May be this?
Count(Customer) / Count(TOTAL {<slice -= {Unknow}>} Customer)
Hi,
Enhancing Pratysh reply.
Convert straight table into pivot table
add calculated dimension as
=if(WildMatch(slice,'0-30 years','30-40 years','40-50 years','50-60 years','60-70 years','70 ans and more'),'Group1',slice)
then go to presentation > select Always Fully Expanded.
Regards,
Prashant Sangle
Hi,
By using If Valuelist method you can able achieve desired output. please check the sample app attached (you have to change the values based on yours).
Age:
LOAD Age,
Customer,
if(Match(slice,'70 ans and more','60-70 years','50-60 years','40-50 years','30-40 years','0-30 years'),'Know',
if(slice='Unknow','Unknow')) as Group,
if(Match(slice,'70 ans and more','60-70 years','50-60 years','40-50 years','30-40 years','0-30 years'),slice) as Know_Slice,
if(slice='Unknow',slice) as Unknow_Slice,
slice
FROM
[Age.xlsx]
(ooxml, embedded labels, table is Sheet1);
Sort:
LOAD * Inline [
Know_Slice,Sort
70 ans and more,6
60-70 years,5
50-60 years,4
40-50 years,3
30-40 years,2
0-30 years,1 ];
Dim:
LOAD * Inline [
Dim
1
2
3
4 ];
Create Straight table
Dimension:
=Pick(Dim,Know_Slice,'Under Total',Unknow_Slice,'Total')
=Pick(Dim,
Count(Customer)/Count( total Customer),
Count({<Group={'Know'}>}Customer)/Count(total Customer),
Count(Customer)/Count(total Customer),
Count(Customer)/Count(total Customer))
Sort Expression:
=Pick(Dim,Sort,8,9,10)