Extra row required - how to add

[#1]

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]

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

[#3]

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]

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]

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.

[#6]

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]

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

[#8]

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

[NareshGuntur]

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

[#10]

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.