topic Re: Total for variable in QlikView

Total for variable

Anonymous — Mon, 06 Feb 2017 09:44:55 GMT

I have expression in table called 'vPass' that return result 0 or 1.

The vPass value is varible called 'vCalTrueFalse':

vPass =$(vCalTrueFalse).

I need to create total field for vPass in TextObject at dashboard.

I tried ' Sum($(=vCalTrueFalse))' but it give me an error: 'Nested aggregation not allowed'.

How can I do it?

Thanks!

Re: Total for variable

stabben23 — Mon, 06 Feb 2017 10:03:16 GMT

Hi Ravit,

And how does you Variable vCalTrueFalse looks like, is it a aggregation?

Re: Total for variable

Anonymous — Mon, 06 Feb 2017 10:06:19 GMT

vCalTrueFalse = if($(vCalPassFail)='Pass', 1,0)

vCalPassFail =

((ParamID =98 or ParamID=99) and $(vResult)<vMCS,'Fail',

((ParamID =102 or ParamID=100) and TestnameID<3 and $(vResult)<vGoodput, 'Fail',

(DashboardID =1 and $(vResult)<Connectivity, 'Fail',

(DashboardID =5 and $(vResult)<TotalSamplingTime, 'Fail',

((ParamID =102 or ParamID=100) and TestnameID>2 and $(vResult)<vGoodput_FDX, 'Fail','Pass')))))

Re: Total for variable

sdmech81 — Mon, 06 Feb 2017 10:06:33 GMT

Hi,

Plss attach sample..

Re: Total for variable

Anonymous — Mon, 06 Feb 2017 10:11:41 GMT

Pass column represent variable vCalPassFail

vPass column represent variable vTrueFalse

What I need to do is show in text object the 'Pass' percentage.

Re: Total for variable

vinieme12 — Mon, 06 Feb 2017 10:12:14 GMT

Try

Sum( AGGR( SUM($(=vCalTrueFalse)) , YourTableDimensions))

or post the app

Re: Total for variable

Anonymous — Mon, 06 Feb 2017 10:16:45 GMT

Its return zero.

How can I post the app?

Re: Total for variable

uacg0009 — Mon, 06 Feb 2017 10:21:15 GMT

Hi Ravit,

If possible could you please attach a sample file?

Because normally I think =sum(vCalTrueFalse) is enough.

Thanks.

Aiolos Zhao

Re: Total for variable

Anonymous — Mon, 06 Feb 2017 13:18:23 GMT

I cannot attached the file (security issue).

Any way, its still return zero.

See attach table and Grade, is there another way to calculate grade?

Re: Total for variable

vinieme12 — Mon, 06 Feb 2017 13:33:10 GMT

see this post on attaching a file

Uploading a Sample

Re: Total for variable

Anonymous — Mon, 06 Feb 2017 14:00:14 GMT

Hi,

Try either one and substitute the correct fieldnames?

SUM(AGGR(SUM(if(MATCH('$(vCalTrueFalse)' ,'PASS'), 1,0)),DashboardName, ReportParam))

SUM(AGGR(SUM(if(MATCH('$(=vCalTrueFalse)' ,'PASS'), 1,0)),DashboardName, ReportParam))

Hopefully that helps!

Re: Total for variable

uacg0009 — Tue, 07 Feb 2017 02:36:59 GMT

Hi Ravit,

Please see the attachment, any questions or problems please tell me.

Thank.

Aiolos Zhao

Re: Total for variable

Anonymous — Tue, 07 Feb 2017 07:19:15 GMT

Got this error in two options...

Re: Total for variable

Anonymous — Tue, 07 Feb 2017 09:52:20 GMT

I cannot open the attched file.

Can you write the formula?

Thanks

Re: Total for variable

uacg0009 — Tue, 07 Feb 2017 10:42:31 GMT

Hi Ravit,

The expression is :

=sum($(vpass))/Count($(vpass))

and the important is the variable :

in script :

set vpass = if(Pass = 'Fail',0,if(Pass = 'Pass',1));

Any questions please tell me.

Aiolos Zhao

Re: Total for variable

Anonymous — Tue, 07 Feb 2017 11:35:21 GMT

I try to do what you suggest and I get '-' result in grade. I will explain what I did:

Since Pass and vPass are varibles (and not table columns) ,

I defined 3 new varibles in my script:

set vsResult =if(DashboardID =1 , ROUND((SUM(ColumnB_Value)/Count(ColumnB_Value))*100, 2),

if ((DashboardID = 5 or DashboardID = 7) ,AVG(ColumnB_Sum),

avg(ColumnB_Value)));

set vtPass = if((ParamID =98 or ParamID=99) and $(vsResult)<vMCS,'Fail',

if((ParamID =102 or ParamID=100) and TestnameID<3 and $(vsResult)<vGoodput, 'Fail',

if(DashboardID =1 and $(vsResult)<Connectivity,'Fail',

if(DashboardID =5 and $(vsResult)<TotalSamplingTime, 'Fail',

if((ParamID =102 or ParamID=100) and TestnameID>2 and $(vsResult)<vGoodput_FDX, 'Fail','Pass')))));

set vsPass = if(vtPass = 'Fail',0,if(vtPass = 'Pass',1));

Then I set this expression in Grade textBox:

=sum($(vsPass))/Count($(vsPass))

See picture:

Get '-' result in grade 😞

What's wrong?

Thanks

Re: Total for variable

uacg0009 — Wed, 08 Feb 2017 02:41:33 GMT

Hi Ravit,

You data seems a little complex, so I didn't use your data to test, Last time I thought that the Pass is a column, now I change my code :

set vtPass = if(DashboardName = 'Connectivity','Fail','Pass');

set vsPass = if($(vtPass) = 'Fail',0,if($(vtPass) = 'Pass',1));

LOAD * INLINE [

DashboardName, ReportParam

Connectivity, Connect

Connectivity, Disconnect

Connectivity, Ping

Connectivity, Scan

Performance, PingLatency

Performance, MCS STA

Performance, MCS AP

Performance, TxGoodput

Performance, RxGoodput

Power, Power

Stability, Link stability

System, PER

System, FA

System, BF

];

And in text object :

=sum($(vsPass))/Count($(vsPass))

The result :

Aiolos Zhao

Re: Total for variable

Anonymous — Wed, 08 Feb 2017 08:04:57 GMT

Thanks for your answer .

I did all you suggest beside this code:

LOAD * INLINE [

DashboardName, ReportParam

Connectivity, Connect

Connectivity, Disconnect

Connectivity, Ping

Connectivity, Scan

Performance, PingLatency

Performance, MCS STA

Performance, MCS AP

Performance, TxGoodput

Performance, RxGoodput

Power, Power

Stability, Link stability

System, PER

System, FA

System, BF

];

What the meanning of this code? Why I need it?

Thanks

Re: Total for variable

uacg0009 — Wed, 08 Feb 2017 08:08:44 GMT

Hi Ravit,

I just pasted all my code to you, that table is my data, because I don't have the data source of yours. So I created a table to make it, actually you don't need it....

Have you solved your problem?

Aiolos Zhao

Re: Total for variable

Anonymous — Wed, 08 Feb 2017 08:12:56 GMT

I see.

No, still I get this error: