topic Re: Really want this to work - substringcount() and aggr() in App Development

Really want this to work - substringcount() and aggr()

jjwild00 — Wed, 05 Oct 2016 22:53:52 GMT

aggr(concat(SelectedValue1), GroupValue1) returns a single value as GroupValue1 is the same for all records. The value is 'ABC'.

Value1 is a field that can be A, B, C or D.

The following calculated dimension fails to return a value:

=SubStringCount(aggr(concat(SelectedValue1), GroupValue1), Value1)

However, the following both return the correct value, 1 in the first case and 0 or 1 in the second case:

=SubStringCount(aggr(concat(SelectedValue1), GroupValue1), 'A')

=SubStringCount('ABC', Value1)

I can put part of the expression that fails to return a value into a variable and it works in QlikView. However, I need to have this work in Sense and its variable handling is not as powerful.

Does anyone see something I can change to get the full expression to work?

Re: Really want this to work - substringcount() and aggr()

sunny_talwar — Thu, 06 Oct 2016 18:08:34 GMT

Not sure how you are using this, can you share a sample?

Re: Really want this to work - substringcount() and aggr()

jjwild00 — Mon, 10 Oct 2016 22:05:28 GMT

I uploaded a sample.

Re: Really want this to work - substringcount() and aggr()

sunny_talwar — Mon, 10 Oct 2016 22:26:19 GMT

Is this what you are after?

Calculated dimension:

=SubStringCount(

Aggr(NODISTINCT Concat(SelectedCallGroup), GroupCallGroup), CallGroup

)

Re: Really want this to work - substringcount() and aggr()

sunny_talwar — Mon, 10 Oct 2016 22:29:22 GMT

Also not sure why you need to use this as a dimension, but usually to show selection from an unlinked field, we can use set analysis like this

Sum({<CallGroup = p(SelectedCallGroup)>}1)

Re: Really want this to work - substringcount() and aggr()

jjwild00 — Mon, 10 Oct 2016 22:59:22 GMT

Thanks for this too. It is a dimension so I can set it to not show on the chart if it is null. If the SubStringCount returns 0, then I do not want the row in the chart.

This is not possible in a measure (Sense term for an expression).