Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

formula(MinString) used in SUM() in table take in the global data set instead of local

Hi All,

In Chart Table, I create a table like the following

DimensionExpressionExpression
ScenarioNo=MinString(myColumnName)SUM($(=MinString(myColumnName)))
1Column00389,404,258
2Column05289,404,258
3Column01889,404,258

There is a one to one mapping between ScenarioNo and myColumnName, picking the ScenarioNo will choose the Column to sum up. Values from different columns (e.g. Column003 / Column052 / Column018) have different sum up value.

I'm not sure which part went wrong, although =MinString(myColumnName) does return the corresponding column name, SUM($(=MinString(myColumnName))) always return the same value which indicates =MinString(myColumnName) within SUM() always return the global min instead of the local min for each ScenarioNo.

Anyone have any idea why this happen and how can I achieve the actual column sum up to each scenario?

i.e. SUM(Column003) for ScenarioNo = 1, SUM(Column052) for ScenarioNo = 2, SUM(Column018) for ScenarioNo = 3 for each row

instead of SUM(Column000) for all three Scenarios.

Thanks very much!

Crystal

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

The dollar expansion $(=MinString(myColumnName)) is done outside the chart context. That's why you always get the sum of Column000. What you can try instead is a pick-match combination:

pick(match(myColumnName, $(=concat(chr(39)&myColumnName&chr(39),','))),

$(=concat('sum('[' &myColumnName&'])',',')))


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

The dollar expansion $(=MinString(myColumnName)) is done outside the chart context. That's why you always get the sum of Column000. What you can try instead is a pick-match combination:

pick(match(myColumnName, $(=concat(chr(39)&myColumnName&chr(39),','))),

$(=concat('sum('[' &myColumnName&'])',',')))


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert, thanks a lot for your advise, it solves my problem! That's a tricky way.

May I ask why the dollar expansion evaluation is done outside the chart context?