topic Re: urgent help required in set analysis expression..? in QlikView

urgent help required in set analysis expression..?

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

Hi Team ,

we have to restrict every chart to avoid the RG Group of MT...

so am adding the below set condistion in all the expressions to avoid it, but am in confusion how to add the same in below expression,

please help me.

=SUM(SMSVolume) / SUM(total <Month> SMSVolume)

How to add below set condition in above expression please help me... Thank you.
{<RG_GROUP =- {"MT *"}>}

in numerator we can add easily but denominator part am getting confused....

Re: urgent help required in set analysis expression..?

fvelascog72 — Thu, 31 May 2018 14:18:19 GMT

Hi,

Maybe:

=SUM({<RG_GROUP =- {"MT *"}>}SMSVolume) / SUM(total <Month> {<RG_GROUP =- {"MT *"}>}SMSVolume)

Saludos

Re: urgent help required in set analysis expression..?

Anil_Babu_Samineni — Thu, 31 May 2018 14:20:58 GMT

Perhaps this?

=SUM({<RG_GROUP -= {"MT *"}>} SMSVolume) / SUM({<RG_GROUP -= {"MT *"}>} total <Month> SMSVolume)

Re: urgent help required in set analysis expression..?

vishalgoud — Thu, 31 May 2018 14:26:49 GMT

Thanks for the Quick reply,

SUM(total <Month> {<RG_GROUP =- {"MT *"}>}SMSVolume) - in this denominator part is this indicates <Month>

even i tried the same and its not showing any expression error. but was just thinking we have to include this month in the set expression or what ..

earlier the expression was =SUM(SMSVolume) / SUM(total <Month> SMSVolume) so what he is achieving from denominator part...

Re: urgent help required in set analysis expression..?

vishalgoud — Thu, 31 May 2018 14:28:17 GMT

thanks for the quick help Anil,

what is this part indicates <Month> from the below expression.

=SUM({<RG_GROUP -= {"MT *"}>} SMSVolume) / SUM({<RG_GROUP -= {"MT *"}>} total <Month> SMSVolume)

Re: urgent help required in set analysis expression..?

fvelascog72 — Thu, 31 May 2018 14:32:43 GMT

Hi,

Now i don´t understand your question.

Do you have a sample app to explain your request?

Saludos.

Re: urgent help required in set analysis expression..?

Anil_Babu_Samineni — Thu, 31 May 2018 14:39:12 GMT

SMSVolume will SUM the value of based on TOTAL Month field. For example

Data set is like below

Load * Inline [

Month, SMSVolume

Jan, 100

Jan, 200

Feb, 300

Feb, 100

];

And measure is this?

Sum(TOTAL <Month> SMSVolume) will return

result:

Jan, 300

Feb, 400

Re: urgent help required in set analysis expression..?

vishsaggi — Thu, 31 May 2018 14:41:45 GMT

This means you are aggregating Sum(SMSVolume) by Month. And the TOTAL qualifier here respects the current selections on Month field. If you do no select any it will sum the volume for all months.

Re: urgent help required in set analysis expression..?

vishalgoud — Thu, 31 May 2018 15:05:37 GMT

Thanks again , while am adding the same to other expression am getting an error in expression msg.

Can you please guide me what is worng in this below expression. it saying error in expression.

=num(MAX(
aggr(sum({<Month={"$(vLastMonth)"},REVENUE_GROUP =- {"MT *"}>}DataVolume),MSISDN)
),'#,##0')

Re: urgent help required in set analysis expression..?

vishalgoud — Thu, 31 May 2018 15:08:50 GMT

=num(MAX(
aggr(sum({<Month={"$(vLastMonth)"},REVENUE_GROUP -= {"MT *"}>}DataVolume),MSISDN)
),'#,##0')

i just changed the underliened part , i kept '- ' sign in front of = instead of keeping it after the equal sign... hope it will not change the meaning of my expression...

Re: urgent help required in set analysis expression..?

vishalgoud — Thu, 31 May 2018 15:23:32 GMT

num(MAX(
aggr(sum({<Month={"$(vLastMonth)"},REVENUE_GROUP -= {"MT *"}>}DataVolume),MSISDN)
),'#,##0')

i just changed the underliened part , i kept '- ' sign in front of = instead of keeping it after the equal sign... hope it will not change the meaning of my expression...

Re: urgent help required in set analysis expression..?

vishsaggi — Thu, 31 May 2018 16:24:07 GMT

You can find the difference of using -= and =- in the below link very well explained by HIC.

Excluding values in Set Analysis

I believe in your scenario, you should be using -= to exclude the values that starts with MT *. Hope this explains all.

Re: urgent help required in set analysis expression..?

Anil_Babu_Samineni — Fri, 01 Jun 2018 03:11:46 GMT

I didn't see any wrong in your expression?