Solved: Extra row required - how to add - Qlik Community

Report Inappropriate Content · ‎2016-02-29

Hi,

I've taken a look around the forums and can't find an answer to this.

I have a simple table:

Status Understanding Team Credibility

Won 9 8 9

Lost 5 2 4

I want to add a new calculated row that shows the difference between won/lost values so values would be 4, 6 and 5. Is this possible and how do I do it?

thanks,

Chris

sunny_talwar · ‎2016-02-29

Check this out:

Script:

Table:

LOAD * Inline [

Status, Understanding, Team, Credibility

Won, 9, 8, 9

Lost, 5, 2, 4

];

Dim:

LOAD * Inline [

Dim

1

2

];

Table

Dimension: Pick(Dim, Status, 'Difference')

Expressions:

1) Pick(Dim, Sum(Understanding), Sum(TOTAL {<Status = {'Won'}>} Understanding) - Sum(TOTAL {<Status = {'Lost'}>} Understanding))

2) Pick(Dim, Sum(Team), Sum(TOTAL {<Status = {'Won'}>} Team) - Sum(TOTAL {<Status = {'Lost'}>} Team))

3) Pick(Dim, Sum(Credibility), Sum(TOTAL {<Status = {'Won'}>} Credibility) - Sum(TOTAL {<Status = {'Lost'}>} Credibility))

View solution in original post

sunny_talwar · ‎2016-02-29

Add an Inline table which will not be connected to your other datasource (Island Table)

Dim:

LOAD * Inline [

Dim

1

2

];

and then use this in your calculated dimension:

Pick(Dim, Status, 'Difference')

Expression

Pick(Dim, Count(Status), Count(TOTAL {<Status = {'Won'}>} Status) - Count(TOTAL {<Status = {'Lost'}>} Status))

Report Inappropriate Content · ‎2016-02-29

Hi Sunny,

Thanks for the reply.

does this mean I need to add a manual table which contain all my dimension values? So Dim 1 and 2 in your response relate to values that appear in understanding (9 and 5) and team fit (8 and 2) or to the 2 status' (won and Lost)?

Thanks,

Chris

sunny_talwar · ‎2016-02-29

It related to neither of them. It just relates to an additional row you need. if you need 10 additional rows then it would be 1 to 10 (For instance if you want to calculate sum, difference, multiplication, division.... and so on). But in your case, since you just need difference, it will always be 1 & 2 for Dim.

reddy-s · ‎2016-02-29

Hi Chris,

You can even do this:

data:

load * inline [

Status,Understanding,Team,Credibility

Won,9,8,9

Lost,5,2,4

];

NoConcatenate

temp:

load Status,

if(Status = 'Lost',Understanding*(-1),Understanding) as Ust,

if(Status = 'Lost',Team*(-1),Team) as Tm,

if(Status = 'Lost',Credibility*(-1),Credibility) as Cred

Resident data;

Concatenate(data)

aggregated:

load 'Summary' as Status,

sum(Ust) as Understanding,

sum(Tm) as Team,

sum(Cred) as Credibility

resident temp;

drop table temp;

As you will not be doing any calculation in the front end, your performance will be better.

Thanks,

Sangram.

Report Inappropriate Content · ‎2016-02-29

I can get it to add 'difference ' as a column but not calculate it correctly as a single row which I'm after. I'm clearly doing something wrong and am very new to Qlik. Qlik only allows me to add a column so do I add a measure instead?

Thanks again.

sunny_talwar · ‎2016-02-29

Would you be able to share your qvf (I think that is what it is called I guess )

Report Inappropriate Content · ‎2016-02-29

I believe the QVF will contain the rest of my data which is a bit more sensitive so not something I can share.

NareshGuntur · ‎2016-02-29

Please find the attached document

=if(Test='Difference','Difference',Status)	Understanding	Team	Credibility
	14	10	13
Won	9	8	9
Lost	5	2	4
Difference	4	6	5

Cheers,

Naresh

Report Inappropriate Content · ‎2016-02-29

Perhaps an easier solution, but not exactly what you asked for:-

Create a new table underneath your existing one, with the difference as the only calculation in it.