topic Re: Set Analysis in Qlikview Straight Table expression in QlikView

Set Analysis in Qlikview Straight Table expression

Anonymous — Mon, 18 Dec 2017 17:30:16 GMT

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:

Alliance	Total Call Count
A1	180
A2	430

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Mon, 18 Dec 2017 17:33:02 GMT

Try this

Sum({<Alliance = p(Alliance), Agent>} calls)

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Thu, 21 Dec 2017 16:26:38 GMT

Thank you Sunny.

I have one more question. is it possible to use 'Agent' as a dimension with the above expression.

Is there any way to get the below result.

Agent	Alliance	Call Count
agent1	A1	180
agent1	A2	430

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Thu, 21 Dec 2017 16:29:34 GMT

What if you use as an expression instead of dimension and then move it all the way to the front?

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Thu, 21 Dec 2017 17:41:56 GMT

Cool. Its working. Thanks a lot Sunny..

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Thu, 21 Dec 2017 18:24:51 GMT

When I select agent1 and agent2 together, I get the below result. Is it possible to show All agents with the above expression.

Because now when I clear the selection, I am getting null values for Agents.

and if more than one agent is linked to same Alliance, it doesn't show those records separately. instead it shows '-' value.

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Thu, 21 Dec 2017 18:26:49 GMT

What would you like to see here?

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Thu, 21 Dec 2017 19:00:41 GMT

When I click on 'Director A' , I will get agent1, agent2 and agent3 associated with Director 'A'.

So when I click 'Direcror 1', I would like to see all agents under him with the below result.

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Thu, 21 Dec 2017 19:11:28 GMT

Here you go

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Thu, 21 Dec 2017 20:00:57 GMT

Great! Thanks a lot Sunny

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Wed, 03 Jan 2018 20:27:29 GMT

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 !

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Wed, 03 Jan 2018 20:30:36 GMT

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

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Wed, 03 Jan 2018 21:12:15 GMT

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

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Wed, 03 Jan 2018 21:51:59 GMT

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

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Wed, 03 Jan 2018 23:07:01 GMT

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))

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Thu, 04 Jan 2018 00:47:05 GMT

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.

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Thu, 04 Jan 2018 00:51:02 GMT

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))

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Thu, 04 Jan 2018 00:53:26 GMT

This

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

Re: Set Analysis in Qlikview Straight Table expression

Anonymous — Thu, 04 Jan 2018 01:58:32 GMT

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?

Re: Set Analysis in Qlikview Straight Table expression

sunny_talwar — Thu, 04 Jan 2018 09:24:35 GMT

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