Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
kcollorig
Contributor II
Contributor II

Subtotal Incorrect in Pivot Table with Aggr Condition

Hello,

I'm having a problem with the subtotal value of my Pivot Table. I'm trying to dynamically assign a value depending on a condition for Dimension 2 and to have the sum of these values for Dimension 1.

Here are the parameters of the table :

Dimension 1 : COMPANY

Dimension 2 : SITE

Column : DATE

Measure : 

If(Count(Distinct[ACTIVITIES]) < Sum(Aggr(sum(STAFF), [DATE],[COMPANY],[SITE])),

  Sum(Aggr(100, [DATE],[COMPANY],[SITE])),

  0
)

So it is working fine for the SITE level (NANTES, SAINT ELOI...) but not for the COMPANY level... The sum does not correspond to the SITE level (100 + 100 = 200).

kcollorig_0-1612967900954.png

 

Any idea ?

Thanks in advance for your help!

Labels (3)
5 Replies
kcollorig
Contributor II
Contributor II
Author

A little up because I'm sure we are close to find the solution... But need your help for this 🙂

marcus_sommer
MVP & Luminary
MVP & Luminary

Maybe something like this:

sum(aggr(

If(Count(Distinct[ACTIVITIES]) < Sum(Aggr(sum(STAFF), [DATE],[COMPANY],[SITE])),

  Sum(Aggr(100, [DATE],[COMPANY],[SITE])),

  0
),

[DATE],[COMPANY],[SITE]))

- Marcus

kcollorig
Contributor II
Contributor II
Author

Thanks for your answer!

Yeah I thought on doing this but unfortunately it doesn't work... Everything is then set to 0...

sonkumamon
Creator
Creator

It may be an issue related to the data model. Do you have duplication of sites values there?

marcus_sommer
MVP & Luminary
MVP & Luminary

Quite often worked this kind of aggregating the results of the inner calculation on their row-level because the set conditions couldn't be applied/fulfilled on the higher TOTAL levels respectively return there other results. But your expression looks a bit uncommon and therefore I suggest to review your object-calculation in regard with the datamodel.

Nevertheless it could be that you need different expressions on the different dimension-levels of your pivot. This could be reached with something like:

if(dimensionality() < 1, expr1, expr2)

To see which row has which level put dimensionality() directly as an expression in your object.

- Marcus