topic Re: MaxString is not working in advanced set analysis in QlikView

MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 14:10:48 GMT

Hi All,

I have below MaxString expression which is working fine for each line.

= Maxstring( {< %BILLINGDOC=, KONV.KSCHL={'ZVSH'} >} KONV.KNUMH)

Now, I have to find max KNUMH value (for that I used above maxstring function) and sum up the respective KBETR values for each MEL numbers. So, I am trying to apply the same in advance set analysis which is not working.

= Sum ( {<

KONV.KNUMH={"= Maxstring( {< %BILLINGDOC= ,KONV.KSCHL={'ZVSH'}>} KONV.KNUMH)" }

KONV.KBETR)

In my case it should show 0 i.e KBETR is 0 for for the KNUMH - 0322567585.

I think Maxstring function is not working in advanced set analysis. Any help or guidance is appreciated.

Note: Please do not ask me to upload the application which is not possible,

Edit: I have uploaded sample document

Re: MaxString is not working in advanced set analysis

andrey_krylov — Thu, 23 Aug 2018 14:18:01 GMT

Try this

= Sum ( {<

KONV.KNUMH={"$(= Maxstring( {< %BILLINGDOC= ,KONV.KSCHL={'ZVSH'}>} KONV.KNUMH))" }

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 14:29:09 GMT

It will be evaluated only once per chart and keep one max value. I need to find max KNUMH value for each MEL numbers or each lines.

Re: MaxString is not working in advanced set analysis

andrey_krylov — Thu, 23 Aug 2018 14:43:37 GMT

As I know set analysis does work this way "once per chart" and not for each line.

Re: MaxString is not working in advanced set analysis

marcus_sommer — Thu, 23 Aug 2018 15:06:23 GMT

You could try it in this way:

= Sum ( {<

KONV.KBETR={"=KONV.KNUMH=Maxstring( {< %BILLINGDOC= ,KONV.KSCHL={'ZVSH'}>} KONV.KNUMH)" }

KONV.KBETR)

- Marcus

Re: MaxString is not working in advanced set analysis

vishsaggi — Thu, 23 Aug 2018 15:10:31 GMT

Never tried this way but just give it a try:

= Sum ({< KONV.KNUMH = {"$(= FirstsortedValue(KONV.KNUMH, -Aggr(Maxstring( {< %BILLINGDOC= ,KONV.KSCHL={'ZVSH'}>} KONV.KNUMH), MEL)))"} >} KONV.KBETR)

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 15:16:52 GMT

I have already tried yours before I posted this question but no luck. Sorry, I could have mentioned what I have tried earlier.

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 15:18:15 GMT

Is this not evaluated once per chart?

Re: MaxString is not working in advanced set analysis

sunny_talwar — Thu, 23 Aug 2018 15:26:51 GMT

Shouldn't this be the expression

=FirstSortedValue({<%BILLINGDOC= ,KONV.KSCHL={'ZVSH'}>} Aggr(Sum({<%BILLINGDOC= ,KONV.KSCHL={'ZVSH'}>} KONV.KBETR), MARA_ENHANCED.NORMT, KONV.KNUMH), -Aggr(Rank(KONV.KNUMH), MARA_ENHANCED.NORMT, KONV.KNUMH))

Re: MaxString is not working in advanced set analysis

sunny_talwar — Thu, 23 Aug 2018 15:31:27 GMT

This works for your sample except for when MEL is 4... I see Cond = 5 to be max... isn't it?

=FirstSortedValue(DISTINCT Aggr(Sum(Sales), MEL, Cond), -Aggr(Cond, MEL, Cond))

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 15:45:33 GMT

My mistake. Sample is working as expected. Unfortunately, the below one is returning the same result as above. I am just trying to play with your expression.

Re: MaxString is not working in advanced set analysis

sunny_talwar — Thu, 23 Aug 2018 16:17:01 GMT

Can you create a new table like this

Dimensions

MARA_ENHANCED.NORMT

KONV.KNUMH

Expression

Sum({<%BILLINGDOC= ,KONV.KSCHL = {'ZVSH'}>} KONV.KBETR)

and share a screenshot

Re: MaxString is not working in advanced set analysis

andrey_krylov — Thu, 23 Aug 2018 16:28:10 GMT

Finally I understood what you want, after Sunny has put her solution) And here is another way

Sum(If(Cond = Aggr(Max(TOTAL<MEL>Cond), MEL, Cond), Sales, 0))

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 16:29:33 GMT

Here is the screenshot.

Re: MaxString is not working in advanced set analysis

sunny_talwar — Thu, 23 Aug 2018 16:40:52 GMT

I wonder why 0322567585 isn't showing up within KONV.KNUMH? How about if you add this as a second expression

Sum({<%BILLINGDOC>} KONV.KBETR)

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 16:48:47 GMT

Yeah.

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 16:53:46 GMT

Sample file is working fine but when I applied the same in my application it is not working.

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 16:59:22 GMT

Sorry Guys. I would like to attach my file but I am not supposed to do it.

Re: MaxString is not working in advanced set analysis

sunny_talwar — Thu, 23 Aug 2018 17:00:49 GMT

This is strange, I am confused as to why 0322567585 isn't showing up? Does it have a zero value for Sum({<%BILLINGDOC>} KONV.KBETR)

Re: MaxString is not working in advanced set analysis

tamilarasu — Thu, 23 Aug 2018 17:05:50 GMT

I was hurry so sorry for the confusion. Yes it is zero value (I have already mentioned in the question).