Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis in Qlikview Straight Table expression

Hi,

I have one question regarding Qlikview Straight table expression. I want to show the straight table Dimension result based on the other field selection but I want to exclude that field selection from expression. Is it possible?

Here is a sample data.

In the attached Test.xls, I have  Director level Data. and in the Test_Alliance.xls file I have Alliance level Data.

I am trying to create a Straight table in which I want to show only those alliances in the Dimension which are linked to Agent when I make Agent selection. But at the same time I want ti ignore the Agent selection in expression and want to calculate those Alliance's total Call count  (irrespective of Agent). Is there any way to do this in Qlikview?

Thanks!

The result Should be:

  

AllianceTotal Call Count
A1180
A2430
25 Replies
Anonymous
Not applicable
Author

Hi,

I have one more question regarding it.

I have added few more calculations in the Pivot table. But the Subtotal which I am getting for last 4 columns is not correct.

I believe its because of Set Analysis which we are using in the expression.

Is there any way that I can get correct subtotal?

Thank you !

sunny_talwar

I thought I just responded to this? Did you delete your and my response?

Anonymous
Not applicable
Author

ohh is it? I couldn't see any reply. Could you please send it again?

Anonymous
Not applicable
Author

Hi Sunny,

Yes, I just realized I mistakenly deleted our conversation. I could find your message in my email.

I tried with that expression instead of label. But it's still not giving me the right subtotal.

Attaching the QVW file. I renamed the old expression as 'Alliance TE% old' and added new one  as 'Alliance TE%'

Regards,

Poonam

sunny_talwar

You didn't use it right, the code is this

Sum(Aggr(sum(TOTAL <Alliance> {<Alliance = p(Alliance), Agent, Director>} Sent)/sum(TOTAL <Alliance> {<Alliance = p(Alliance), Agent, Director>} calls), Agent, Alliance))


Capture.PNG

Anonymous
Not applicable
Author

Thank you Sunny for the reply.

Its giving me proper subtotal. But for 'Agent TE%' expression its not giving me the correct subtotal.

Also, Is there any way that I can get Average instead of subtotal.

Attaching the Application and result data.

sunny_talwar

May be use Avg(Aggr()) Instead of Sum(Aggr())

Avg(Aggr(sum(TOTAL <Alliance> {<Alliance = p(Alliance), Agent, Director>} Sent)/sum(TOTAL <Alliance> {<Alliance = p(Alliance), Agent, Director>} calls), Agent, Alliance))

sunny_talwar

This

Avg(Aggr(sum(Sent)/ sum(calls), Director, Agent, Alliance))


Capture.PNG

Anonymous
Not applicable
Author

Cool, That's great!

I have one question. To calculate ' Alliance TE%' we are using below formula:

Sum(Aggr(sum(TOTAL <Alliance> {<Alliance = p(Alliance), Agent, Director>} Sent)/sum(TOTAL <Alliance> {<Alliance = p(Alliance), Agent, Director>} calls), Agent, Alliance))

Why didn't we add 'Director' in Aggregation at the end?

sunny_talwar

If you read my deleted response I did talk about adding Director to your Aggr() function.... check my response out there