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: 
Not applicable

Extra row required - how to add

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

1 Solution

Accepted Solutions
sunny_talwar

Check this out:

Capture.PNG

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

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

Not applicable
Author

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
Master II
Master II

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.

Not applicable
Author

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?

Capture.PNG

Thanks again.

sunny_talwar

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

Not applicable
Author

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

NareshGuntur
Partner - Specialist
Partner - Specialist

Please find the attached document

=if(Test='Difference','Difference',Status) Understanding Team Credibility
14 10 13
Won989
Lost524
Difference465

Cheers,

Naresh

Not applicable
Author

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.