topic Re: Nested Set Analysis - New in QlikView

Nested Set Analysis - New

ashish_2511 — Wed, 25 Nov 2020 16:16:04 GMT

Dear Community

I know this topic has been discussed a lot but I couldn't find anything relevant to my problem. So here's my problem

I have to write a pivot table expression like this -

Count(Distinct {<only({<$(vServTarg_ExcludeSelections)>}MTM_COUNTER)=1,
Sum({<BV_DETAIL_LATEST_FLAG={1}, $(vServTarg_ExcludeSelections)>}BV_D_COUNTER)=0,
Sum({<YM_ACTI={$(vMonth0),$(vMonth1),$(vMonth2),$(vMonth3),$(vMonth4),$(vMonth5)}, ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>}ACTIVITY_COUNTER)>0 >}
CONTACT_ID)

Unfortunately it won't work. Also I cannot write it using 'If' as it won't allow nested aggregation.

Please suggest.

Thanks in advance

Sneh

Re: Nested Set Analysis - New

jyothish8807 — Fri, 09 Mar 2018 06:58:55 GMT

Hi Sneh,

Can you please explain what are you actually trying to achieve ?

The expression do not make much sense to me.

Br,

Re: Nested Set Analysis - New

vijetas42 — Fri, 09 Mar 2018 07:02:22 GMT

Please specify your requirement that what you are trying to achieve.

Re: Nested Set Analysis - New

ashish_2511 — Fri, 09 Mar 2018 07:10:41 GMT

Hi Jyotish

I want to get the Count contactId s. There are 3 conditions that I need to check. The expression is wrong I know but I didn't know how to make it right. so please advise how to make it work. basically it's set analysis within set analysis.

Hope that makes sense.

Re: Nested Set Analysis - New

ashish_2511 — Fri, 09 Mar 2018 07:10:59 GMT

Re: Nested Set Analysis - New

jonathandienst — Fri, 09 Mar 2018 07:15:16 GMT

Your syntax is wrong. I suggest that you explain in more detail what you are trying to achieve.

If the expression is not working, why are using variables? Get the expression working in parts and then convert to variables later. Trying to resolve something like this is a lot more complex and it is difficult to make suggestions because you have not provided any information about the variables or your requirements.

Re: Nested Set Analysis - New

vijetas42 — Fri, 09 Mar 2018 07:17:10 GMT

what are the basic three conditions you want to check in above expression there is so much of confusion

Re: Nested Set Analysis - New

jonathandienst — Fri, 09 Mar 2018 07:17:53 GMT

>>There are 3 conditions that I need to check.

Its impossible to make useful suggestions with such a vague comment.

Re: Nested Set Analysis - New

ashish_2511 — Fri, 09 Mar 2018 07:26:43 GMT

Hi Jonathan

Let me remove the variables, that leaves my expression like this -

Count(Distinct {<only(MTM_COUNTER)=1,
Sum({<BV_DETAIL_LATEST_FLAG={1}>}BV_D_COUNTER)=0,
Sum({<ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>}ACTIVITY_COUNTER)>0 >}
CONTACT_ID)

I know this is wrong. Want suggestions to make it right.

I want to achieve - Count(Distinct Contact ID)

There are 3 conditions I want to check in my set analysis -

only(MTM_COUNTER)=1

Sum({<BV_DETAIL_LATEST_FLAG={1}>}BV_D_COUNTER)=0

Sum({<ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>}ACTIVITY_COUNTER)>0

Help me construct a set analysis expression using this. 'If' is also fine if not set analysis (but if won't allow nested aggregation).

Thanks!

Re: Nested Set Analysis - New

jonathandienst — Fri, 09 Mar 2018 07:44:26 GMT

Much clearer

You will need an Aggr() to do nested aggregations like this and Aggr() needs some dimensions, which are usually the same as your chart dimensions. Lets get it working with an if for now:

Count(Aggr(

If(Sum({<BV_DETAIL_LATEST_FLAG = {1}>} BV_D_COUNTER) = 0

And

Sum({<ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>} ACTIVITY_COUNTER) > 0

And

Only(MTM_COUNTER) = 1,

Count)

, <dim1>, <dim2>))

<dim1>, <dim2>, .... are the chart dimensions. Not these must be physical fields, not calculated dimensions. Add them as comma separated list of field names.

Re: Nested Set Analysis - New

jyothish8807 — Fri, 09 Mar 2018 07:47:28 GMT

Try like this then,

count(distinct If([MTM_COUNTER]=1 and Sum({<BV_DETAIL_LATEST_FLAG={1}>}BV_D_COUNTER)=0 and Sum({<ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>}ACTIVITY_COUNTER)>0,[Contact ID]))

Br,

Re: Nested Set Analysis - New

ashish_2511 — Fri, 09 Mar 2018 08:48:35 GMT

Fantastic! this is what I was looking for. A small problem though - I've calculated dimensions . do you have something else that would work with calculate dimensions?

Thanks for all the help so far

Cheers!

Sneh

Re: Nested Set Analysis - New

ashish_2511 — Fri, 09 Mar 2018 08:50:17 GMT

Thanks for that. I'm afraid this isn't working as nested aggregation is not possible in 'If' but having a aggr function around will make it work as Jonathan suggested above.

Thanks!

Re: Nested Set Analysis - New

sunny_talwar — Fri, 09 Mar 2018 12:32:01 GMT

May be you can perform the calculated dimension's calculation in the script?

Re: Nested Set Analysis - New

gloria_bertini — Fri, 09 Mar 2018 17:04:06 GMT

Hi,

i don't know if it can be useful for you case, but maybe you can try syntax for nested set analys like proposed in this thread

set-analysis lost customers

regards