Set Analysis calculation excluding user filters

[Lou_Lab]

Hello,

I need help with a set analysis.
I have a database in which a line represents a batch with a reception date, a type and a weight.
For each batch, I'd like to calculate a ratio = a constant defined by type / Total weight received per month of Receipt Date by type, which would remain independent of the user's filters

I've tried something like this without success
Sum({<Type={'A'}>}Constant A)
/
Sum( Aggr( Sum( {1<Type={'A'}>}Weight),ReceptionMonthName))

Thanks for your help !

[Mark_Little]

HI,

Can you provide some more details, maybe include a worked example?

I think your are probably looking at  the <Total> function in your set analysis, but not a hundred percent on the use case

[Lou_Lab]

Hi @Mark_Little,

Sorry I can't export a worked exemple because I don't have the right to do it.

Anyway, I tried too with the total function,  but in that case I don't know where I can add the setting Type=A like :

Sum( Total <ReceptionMonthName, Type='A'> Weight)

Because if I let the condition Type='A' in the set analysis, and I filter my table on a batch it won't be the total af all the batches but just the weight of this specific batch...

I don't know if my explanations are clear ? 🤔

[marcus_sommer]

It's not quite clear what exactly you want to do. Therefore a few points to consider. If a calculation should ignore all or certain selections the set analysis might be needed to applied to all expression-parts and not just to the inner aggregation, for example:

sum({ ...} aggr({...} sum({...} Field), Dim))

To ignore selections you may apply something like:

{ < Field1, Field2 >}
{1 ..... }
{1 ..... } TOTAL

This is just related to the selections - if you need to ignore all or a certain dimensionality within the calculation you need to apply a TOTAL statement, like:

sum(Total Field)
sum(Total <Dim1, Dim2> Field)

Just use a table-chart and put several variances of the above shown examples into separate expressions side by side and you could easily comprehend by the results what happens.

[BrunPierre]

Try using this as the denominator.

Sum({1} Aggr( Sum( {1<Type={'A'}>} Weight), ReceptionMonthName))

[Mark_Little]

No sorry, 

Still not clear,

Can you not just make up a couple of lines of data up and show us the logic?

[Lou_Lab]

Hello,

First of all thanks for your concern !

I built an Excel sample file to explain the case:

1 - In the Tab Database Col A to F it's the row datas

2 - In the Tab Total by month you will find 2 pivot table to calculate the total by month with the filters I want to apply

3 - In the Tab Database col G, the rate calculation based on the constants and the total.

The issue in Qlik with the currents formulas is if I use some filters the total by month can change.

[marcus_sommer]

You may try something like:

sum({< Site = {'A'}, Product>} total <Month> Weight)

which would restrict the dataset to Site = A and ignoring any selections within the Product and ignoring all dimensions for the aggregation unless the Month. Like above mentioned is using a table-chart with some variances of the set analysis and total statements a very easy and simple way to comprehend the logic - especially if you reduced the dataset to maybe 10 - 20 records to keep all results easy to grasp at a glance.

[Lou_Lab]

Thanks @marcus_sommer it works !

The last inconvenient of this formula is that I have to add all the dimension that can be used by the users in the set analysis to ignore them. I would have prefered something that ignore all the dimension by default except the one I want to keep. Any Ideas ?

[marcus_sommer · Accepted solution]

You may play with the identifier 1, like:

sum({1< Site = {'A'}, Product>} total <Month> Weight)

or you may apply all wanted fields with an expression-result per adhoc-variable or maybe with an outsourced variable, like:

sum({< Site = {'A'}, $(='[' & concat({< $Field -= {'Site'}>} $Field, '], [') & ']')>} total <Month> Weight)