topic Re: Set Analysis in QlikView

Set Analysis

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

Hi,

I am trying write on expression in set analysis.

=Sum({$<Distinct [ASDP Name] =>}[CSNUM])

=(AGGR(SUM(DISTINCT [CSDEN]),[ASDP Name]))

basically i need For Distinct ASDP Name what is the sum of NUmerator.

Can some one help me here.

Best Regards,

Re: Set Analysis

Anil_Babu_Samineni — Wed, 17 May 2017 15:26:37 GMT

Expression seems not the correct syntax

=Sum({$<Distinct [ASDP Name] =>}[CSNUM])

Use this

=Sum({$<[ASDP Name] =>} DISTINCT [CSNUM])

Seems, second expression correct to me, Is there any wrong output?

Re: Set Analysis

dunnalahk123 — Wed, 17 May 2017 15:33:41 GMT

Hi Babu,

Thank You.

But i need For Distinct ASDP Name i need sum of numerator.

Best Regards,

Re: Set Analysis

Anil_Babu_Samineni — Wed, 17 May 2017 15:35:55 GMT

Again Confused, And field looks like Character. How do you sum for Character. May explain bit more with sample data

Re: Set Analysis

dunnalahk123 — Wed, 17 May 2017 15:45:17 GMT

Hi,

I attached APP above.

Best Regards,

Re: Set Analysis

neelamsaroha157 — Wed, 17 May 2017 15:46:03 GMT

It would be easier if you can share sample data.

Can one CSDEN have multiple CSNUM??

Re: Set Analysis

dunnalahk123 — Wed, 17 May 2017 15:50:32 GMT

HI,

For CS DEN is nothing but my Denominator or CSNUM is nothing but my Numerator always it will be 1 or 0 it is my Calculated fields .

if you go to raw details tab For one ASDP NAME have mutiple rows.

So in piviot when i do sum for one ASDP Name it should be counted only once.

Best Regards,

Re: Set Analysis

neelamsaroha157 — Wed, 17 May 2017 16:07:41 GMT

Check this out.

I didn't get what is the issue that you are facing but I have added multiple columns to reflect the result set.

Let me know if this helps.

Re: Set Analysis

dunnalahk123 — Wed, 17 May 2017 16:14:35 GMT

Hi Neelam,

Thank you.

Basically when i collapse the pivot for AMS CSNUM should be 13 which is my Numerator and CS DEN will be 18 which is my Denominator.

So it's not working as Expected at this moment.

Best Regards,

Re: Set Analysis

neelamsaroha157 — Wed, 17 May 2017 18:36:25 GMT

Check this out.

Re: Set Analysis

dunnalahk123 — Wed, 17 May 2017 19:14:29 GMT

Hi Sehrawat,

Thank You So much, we are almost there.

One thing in my saved attachment which i just upload again at original post i have selected one ASDP name which is there in both EMEAHQ and SPAIN .For EMEAHQ it shows 0 as NUM and 1 as DEN which is correct but for SPAIN it shows both num and Den as 0. which it should not be.

For SPAIN as well it should show same number which it shows for EMEAHQ as 0 and 1.

If possible can we tweak this.

But thanks a lot for all your help. Really Appreciated. 🙂 🙂

Re: Set Analysis

neelamsaroha157 — Wed, 17 May 2017 19:22:34 GMT

You can add 'Subregion' in the aggr with '[ASDP Name]'.

And you are welcome

Re: Set Analysis

neelamsaroha157 — Wed, 17 May 2017 19:23:44 GMT

Let me know if it gives the desired output. I tried on my end and it seems to work.

Re: Set Analysis

dunnalahk123 — Wed, 17 May 2017 19:29:35 GMT

Hi neelamsaroha1575

Thank You so Much for all your time. it works now. 🙂

Have a nice day 🙂