Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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)
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
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 ? 🤔
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.
Try using this as the denominator.
Sum({1} Aggr( Sum( {1<Type={'A'}>} Weight), ReceptionMonthName))
No sorry,
Still not clear,
Can you not just make up a couple of lines of data up and show us the logic?
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.
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.
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 ?
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)