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!
It still doesn't work for dimensionality 5
i export it to excel from the qv. Look at the test column
Store | Floor | Floor No | Vendor | Brand | Dimensionality() | m2 | % m2 | test |
Total | 0 | 3.513 | 100,00% | 100,00% | ||||
GOLDEN HALL | Total | 1 | 2.646 | 75,34% | 75,34% | |||
GOLDEN HALL | 1ος-LOBBY | Total | 2 | 10 | 0,38% | 0,38% | ||
GOLDEN HALL | 1ος-LOBBY | 1st | Total | 3 | 10 | 100,00% | 100,00% | |
GOLDEN HALL | 1ος-LOBBY | 1st | Kalamas | Total | 4 | 10 | 100,00% | 100,00% |
GOLDEN HALL | 1ος-LOBBY | 1st | Kalamas | io | 5 | 10 | 0,38% | 100,00% |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ JEANS | Total | 2 | 215 | 8,13% | 8,13% | ||
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ JEANS | 1st | Total | 3 | 215 | 100,00% | 100,00% | |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ JEANS | 1st | Kalamas | Total | 4 | 215 | 100,00% | 100,00% |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ JEANS | 1st | Kalamas | a | 5 | 83 | 3,14% | 38,57% |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ JEANS | 1st | Kalamas | fdg | 5 | 58 | 2,20% | 27,04% |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ JEANS | 1st | Kalamas | xz | 5 | 74 | 2,80% | 34,39% |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΠΟΧΙΑΚΟ | Total | 2 | 110 | 4,15% | 4,15% | ||
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΠΟΧΙΑΚΟ | 1st | Total | 3 | 110 | 100,00% | 100,00% | |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΠΟΧΙΑΚΟ | 1st | Kalamas | Total | 4 | 110 | 100,00% | 100,00% |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΠΟΧΙΑΚΟ | 1st | Kalamas | rt | 5 | 104 | 3,92% | 94,54% |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΠΟΧΙΑΚΟ | 1st | Kalamas | we | 5 | 6 | 0,23% | |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΣΩΡΟΥΧΑ | Total | 2 | 12 | 0,45% | |||
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΣΩΡΟΥΧΑ | 1st | Total | 3 | 12 | 100,00% | ||
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΣΩΡΟΥΧΑ | 1st | Kalamas | Total | 4 | 12 | 100,00% | |
GOLDEN HALL | 1ος-ΓΥΝΑΙΚΕΙΑ ΕΣΩΡΟΥΧΑ | 1st | Kalamas | jhi | 5 | 12 | 0,45% |
This should get you those values, I think
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),
Sum(m2)/Sum(TOTAL <Store, Floor, Vendor> m2))))
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)
Thank you very much!!!! It was so difficult for me to find!