Qlik confused

[Anonymous]

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!

[rubenmarin]

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?

[sunny_talwar]

Or this?

If(Dimensionality() = 1, Sum(m2)/Sum(TOTAL m2), Sum(m2)/Sum(TOTAL <Store> m2))

[Anonymous]

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.

[rubenmarin]

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.

[Anonymous]

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%.

[Anonymous]

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.

[Anonymous]

=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

• I tried the above but it doesn't work, look below can you plz help me to continue the expression if there is dimensionality 4, 5? I think we are very close, it works for the most rows

=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)

• But i want the calculation to be per row and to the total of the before dimension, not to grand total. Every dimension should be calculated to the above total. And the total to subtotal and then to the grandtotal. For example the total of the dimensionality 5 should be calculated to the total of dimensionality 4 and for then dimensionality 4 to the total of dimensionality 3...

[rubenmarin]

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.

[sunny_talwar]

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)))))