Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I want to calculate the share to sales and m2 per row, totals and subtotals. I am using dimensionality but not in big success! There is problem because i am using different and not standard dimensions.
It makes right the % calculation at rows but there is an issue at totals, you can see it is calculated 100% but I want to take the above dimension and calculated to that total. Only the Dimensionality 1 (‘Store’ in my example) should give 100%
I attach the qlik report ‘example_1’ and my excel file ‘data_1’ (look column H(%m2) and K(%Sales) where I am showing the calculation I want to achieve in qlikview. Plz help i am trying for weeks!
Thanks in advance!
I can only work some dynamism on dimension order disabling the possibility to pivot the table (in presentation tab) and using cyclic groups to select the fields, this way I can know wich field is in each dimension and configure the 'total' based on the fields selected for each dimension.
PFA, I only changed the expression for % m2 (and the dimensions, using groups)
Hi Kyparisia, the store total has Dimensionality()=3, and you can use the same TOTAL expression you are using for lines, just group by store, instead of store and floor, in example for m2:
=if (Dimensionality() = 3,
m2/sum(Total<Store> m2),
m2/sum(Total<Store,Floor> m2))
Is this what you are looking for?
Or this?
If(Dimensionality() = 1, Sum(m2)/Sum(TOTAL m2), Sum(m2)/Sum(TOTAL <Store> m2))
Good morning! Thanks you very much for your reply!
But it dosn't work for dimencionality 1 , 2
Any idea? I am attching the report.
Good morning, I don't fully understand the requirements... in any case the solution is similar: playing with Dimensionality() and TOTAL:
=if (Dimensionality() <= 2,
m2/sum(Total m2), //All the m2
if (Dimensionality() = 3,
m2/sum(Total<Store> m2), // m2 by store
m2/sum(Total<Store,Floor> m2)) // m2 by store and floor
Or it also can be just:
m2/sum(Total m2) //For all dimensions
Or:
m2/sum(Total {1} m2) //The Total part won't change with selections, (same % per row)
It depends on what you exactly want, but with this examples you can work it.
Hello Sunny, thank u very much for your help. I wrote the expression you suggest but it only works for dimencionality 1 , 2. If you look closer at the circled example for dimencionality 5 (Brand: 'io') it should give me 100% not 0,38%.
If you have the time plz check my excel file, the marked columns. I have made a lot of search and i think it is very difficult to find the solution! I think we may use something like that..
Pick(Dimensionality()+1,
m2/aggr(sum(TOTAL m2),Store),
m2/aggr(sum(TOTAL m2),[Floor no]'
m2/aggr(sum(TOTAL m2),Brand),
)
beacause dimensions aren't standard, it depends on the report usage( the most of them are ad hoc). We may have to promote Vendor in the first dimension or demote it at the end, or delete. I want an expresion that it works if i add or delete dimensions or if i change the order. And in any way it should made the calculation at the total of the above dimension.
=if (Dimensionality() <= 2,
m2/sum(Total m2), //All the m2
if (Dimensionality() = 3,
m2/sum(Total<Store> m2), // m2 by store
m2/sum(Total<Store,Floor> m2)) // m2 by store and floor
=if (Dimensionality() = 1,
m2/sum(Total m2),
if (Dimensionality() = 2,
m2/sum(Total<Store> m2),
m2/sum(Total<Store,Floor> m2))
)
Or it also can be just:
m2/sum(Total m2) //For all dimensions
Or:
m2/sum(Total {1} m2) //The Total part won't change with selections, (same % per row)
This last paragraph is a good explanation of what you need, let me finish some things I'm doing now and, if you don't have get a response yet, I try to find a dynamic solution.
May be this?
If(Dimensionality() = 1, Sum(m2)/Sum(TOTAL m2),
If(Dimensionality() = 2, Sum(m2)/Sum(TOTAL <Store> m2),
If(Dimensionality() = 3, Sum(m2)/Sum(TOTAL <Store, Floor> m2),
If(Dimensionality() = 4, Sum(m2)/Sum(TOTAL <Store, Floor, Vendor> m2), Sum(m2)/Sum(TOTAL <Store> m2)))))