37 Replies Latest reply: Aug 9, 2018 4:56 AM by Matt Wilson

# Assign a 'fixed' calculation to a column in table

Hey there

can anyone point me in the right direction here with adding a column to a table?  As per below, its logged/interactions total.    The total obviously will change based on the selection, but I need it as the same value  in each row, due the the data simply being pasted into a legacy spreadsheet.

Many thanks!

• ###### Re: Assign a 'fixed' calculation to a column in table

May be this

Avg(TOTAL Aggr(Logged/Interactions, name_uq))

• ###### Re: Assign a 'fixed' calculation to a column in table

Thanks for the speedy reply Sunny, and the link.  I thought it might need set analysis though I am not yet familiar with this.

I havent read the link yet, I have however tried your expression and it didn't seem to work.

• ###### Re: Assign a 'fixed' calculation to a column in table

This might help!

• ###### Re: Assign a 'fixed' calculation to a column in table

Logged and Interactions are fields or expressions?

• ###### Re: Assign a 'fixed' calculation to a column in table

Good question.  They are expressions in the master items.  However Logged is also a field name, which probably isnt a good idea/practice (just realised).  Logged expression = sum(Logged)

• ###### Re: Assign a 'fixed' calculation to a column in table

So, try something like this

Avg(TOTAL Aggr(Sum(Logged)/Sum(Interactions), name_uq))

• ###### Re: Assign a 'fixed' calculation to a column in table

you could use the below expression:

sum(total Logged) / sum(total Interactions)

• ###### Re: Assign a 'fixed' calculation to a column in table

Thanks to you both.  I have got it working, though need to come back with a clearer head, I have confused myself a little.  Having the field names the same as the expression, and a sum + another sum in one of them, I just need to relook at and understand Sunnys answer.  It only lets me mark 1 answer as correct

anyway Andreas method is working, though for some reason I had to deconstruct the expression

sum(Total Logged)/sum (total[ava_Voice]+[ava_NV]-[ava_Orders])

I'll have a play and come back

• ###### Re: Assign a 'fixed' calculation to a column in table

Hi friends, I am happy with the current resolution, in that its doing the job but my understanding is a bit foggy (awaiting delivery of my cookbook ).  To be honest I want to readdress my data model when I get into the opportunity to get more into the Qlik scripts/

So yesterdays exercise, happy days

Now though I need to take a step back and I have had to tamper with the Log figures.  That should never be higher than the interactions!  So if Logged > Interactactions, I set the interaction and logged figure to match thus giving a 100% maximum possible

So here I have fixed the data, issue is that the total column isn't reflecting properly in the %, whilst the individual rows/ciulmns areare

I have played with the 'total' calculation drop down which is set to auto, but none of the other options allow me to put in say, an expression.  Is this normal behaviour?  I need to address that!

• ###### Re: Assign a 'fixed' calculation to a column in table

Once again it would be helpful to know what your expression for Avg, Logged and Interactions is? or else share a sample so that we can see that of our-self.

• ###### Re: Assign a 'fixed' calculation to a column in table

Thanks Sunny, sorry if a bit vague

Logged Expression = Sum(Logged)

Interactions Expression (in this capped example) =

if

(

Logged >= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  > 0 //and at least one interaction

,Logged //then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  //or interactions as normal

)

Avg Expression =

sum(Logged)/

if

(

Logged >= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  > 0 //and at least one interaction

,Logged //then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  //or interactions as normal

)

• ###### Re: Assign a 'fixed' calculation to a column in table

Okay, so this make sense... what is the total number you expect to see from the image that you have posted?

• ###### Re: Assign a 'fixed' calculation to a column in table

Image1, all good

• ###### Re: Assign a 'fixed' calculation to a column in table

Are you sum of rows for total of Interaction column? Try this

sum(Logged)/

Sum(Aggr(

if

(

Logged >= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  > 0 //and at least one interaction

,Logged //then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  //or interactions as normal

),

name_uq))

• ###### Re: Assign a 'fixed' calculation to a column in table

Thanks Sunny

So the sum of rows for this Interactions column is correct (492876)

The sum of the rows for the Logged column is correct (295465)

Your expression adding the bits in orange (well copying the expression in) validates (as in ok) but just returns a dash on all rows "-"

• ###### Re: Assign a 'fixed' calculation to a column in table

What is/are your chart dimension/s? Is it name_uq or do you have calculated dimension?

• ###### Re: Assign a 'fixed' calculation to a column in table

It is simply a field called name_uq where I just change the label.  Nothing more than that

• ###### Re: Assign a 'fixed' calculation to a column in table

Can you check if this works or not

Sum(Aggr(

if

(

Logged >= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  > 0 //and at least one interaction

,Logged //then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  //or interactions as normal

),

name_uq))

• ###### Re: Assign a 'fixed' calculation to a column in table

Zero in all rows

• ###### Re: Assign a 'fixed' calculation to a column in table

Oh my bad... I think Logged is referencing to Sum(Logged), right? Try this

sum(Logged)/

Sum(Aggr(

if

(

sum(Logged)>= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  > 0 //and at least one interaction

,sum(Logged)//then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  //or interactions as normal

),

name_uq))

• ###### Re: Assign a 'fixed' calculation to a column in table

Yes!! That's (almost) it

Thought its coming up as 75.96% percent which is too high

SO. on individual name_uq fields its correct.  And if I filter on chunks of name_uqs with a % against them its correct

Something is skewing it when I dont apply a filter.  I suspect its something to so with if there is a zero in one or both of the Logged and Interaction columns, and a dash "-" comes up in the Avg field?

(By the way I know some of this is probably basic to you but its much appreciated and I have learned a couple of things. one of which is not to name measures in the library the same as a field name, too confusing.  One object of the same name per app would be desirable)

• ###### Re: Assign a 'fixed' calculation to a column in table

Can you see what value does this show on the total row?

Sum(Aggr(

if

(

sum(Logged)>= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  > 0 //and at least one interaction

,sum(Logged)//then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  //or interactions as normal

),

name_uq))

(By the way I know some of this is probably basic to you but its much appreciated and I have learned a couple of things. one of which is not to name measures in the library the same as a field name, too confusing.  One object of the same name per app would be desirable)

I am glad you are able to learn from our interaction .

• ###### Re: Assign a 'fixed' calculation to a column in table

Hmmm 388,982!? (as a fixed number, and 38898200% as a %)!

• ###### Re: Assign a 'fixed' calculation to a column in table

So, when you export your above chart into Excel and sum all the rows... do you get 388,982 or 492,876? And out of the two you sure you want to divide by 492,876?

• ###### Re: Assign a 'fixed' calculation to a column in table

Good question.  The target here is obviously measuring interactions vs logs.  Its "ok" to have a log with 0 interaction (well thats a different question and part of the scope), but its ok to have a log with a '0' interaction (hence why I have to make sure you cant get over 100%)

I am quite sure I do indeed want 492,876

• ###### Re: Assign a 'fixed' calculation to a column in table

I have attached the data extracted from the latest solution

• ###### Re: Assign a 'fixed' calculation to a column in table

What is this UnKnown? Is that null Agent?

• ###### Re: Assign a 'fixed' calculation to a column in table

Hmm I need to check that.  They are new staff which either have logs but no interactions, or interaction with no logs grouped together I am thinking.  They are not yet added to our “staff”/personal view I believe.  Do you think that could affect things?

They actually come up with a “-“ on the table.  Is that how nulls behave in Qlik?  I notice now when I click the field it doesn’t do anything

• ###### Re: Assign a 'fixed' calculation to a column in table

Do you think that could affect things?

I think it is def. having an impact here because the total you expected was 492,876 and total you got with my expression was 388,982 and the difference between the two is 103,894.

They actually come up with a “-“ on the table.  Is that how nulls behave in Qlik?  I notice now when I click the field it doesn’t do anything

Yup that is def. null then.... can you may be create another field in the script like this

If(Len(Trim(Agent)) = 0, ' ', Agent) as New_Agent

and then try this

Sum(Aggr(

if

(

sum(Logged)>= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  > 0 //and at least one interaction

,sum(Logged)//then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  //or interactions as normal

),

New_Agent))

• ###### Re: Assign a 'fixed' calculation to a column in table

Great. I need to figure out where in my script it should go, its based on SQL views.  I just gave it a quick try but will test in the morning now.  Thanks again for your help Sunny, have a great rest of day/evening

• ###### Re: Assign a 'fixed' calculation to a column in table

grrrrr.  I am struggling a bit to work out where to put this is my script to handle the nulls

So it should be name_uq in the script (?) which is the field name, Agent is just the label

If(Len(Trim(Agent)) = 0, ' ', Agent) as New_Agent

So first thing I want to understand, Len(Trim(Agent)) = 0, I am surprised the null would return 0, not another null (as it would in sql), is that expected?

second thing, does the load order of table matter in the script?.

This data in its simplest form is coming from 3 views. This is/should be straightforward I guess, but after much tinkering I got the data model working.  The views are 2 fact tables I guess, and a link table.  Again I want to revisit this when my understanding is better and move some of the logic to Qlik and out of SQL where I am more comfortable

My script is like so:

[ava_HandledByAgent]:

[Voice] AS [ava_HandledByAgent.Voice],

[Orders] AS [ava_HandledByAgent.Orders],

[Customer_Service] AS [ava_HandledByAgent.Customer_Service],

[Non-Voice] AS [ava_HandledByAgent.Non-Voice],

"Voice",

"Orders",

"Customer_Service",

"Non-Voice"

FROM "ava_HandledByAgent";

[magusername], //or/and does it go here which is the 'link table' and joins up with the mag_LoggedbyAgent views

name_uq, //THIS IS A OUR MAIN DIMENSION AND IS UNIQUE IN THE VIEW (primary key in the underlying table

DATE([CallDate]) AS [Calendar_Date-CallDate],

[reportstoname],

exclude,

Notes

;

"name_uq",

"CallDate",

"reportstoname",

"exclude",

"Notes"

FROM "vLinkTable" WHERE reportstoname is not null; //reports to name is non nullable in staff table

[mag_LoggedbyAgent]:

[username], //finally, the 2nd view.  Does the order of script matter?  This one is working and I sure dont want to break it!!!!  Just the damn nulls

Date([magDate]) AS magDate,

[Logged],

"magDate",

"Logged"

FROM "mag_LoggedbyAgent";

@dunn

• ###### Re: Assign a 'fixed' calculation to a column in table

So it should be name_uq in the script (?) which is the field name, Agent is just the label

If(Len(Trim(Agent)) = 0, ' ', Agent) as New_Agent

Yes, use name_uq or whatever the field name is....

So first thing I want to understand, Len(Trim(Agent)) = 0, I am surprised the null would return 0, not another null (as it would in sql), is that expected?

Len(Trim( for null gives 0 in Qlik... not sure how it work elsewhere... but the reason to use Len(Trim(... is to also check for blank spaces which might look like nulls but they actually are not... in your case.. we know those are null... so you can def use IsNull(name_uq)

second thing, does the load order of table matter in the script?.

It should not matter, but what made you ask this? I might be missing something here and that is why I ask....

In your script... you are doing Len(Trim()) on AgentAction? instead or name_uq? I just want to clarify before we move forward...

• ###### Re: Assign a 'fixed' calculation to a column in table

So it should be name_uq in the script (?) which is the field name, Agent is just the label

If(Len(Trim(Agent)) = 0, ' ', Agent) as New_Agent

Yes, use name_uq or whatever the field name is....

Cool

So first thing I want to understand, Len(Trim(Agent)) = 0, I am surprised the null would return 0, not another null (as it would in sql), is that expected?

Len(Trim( for null gives 0 in Qlik... not sure how it work elsewhere... but the reason to use Len(Trim(... is to also check for blank spaces which might look like nulls but they actually are not... in your case.. we know those are null... so you can def use IsNull(name_uq)

yeah in sql len or trim of null is (or should be) null, ansi standard

second thing, does the load order of table matter in the script?.

It should not matter, but what made you ask this? I might be missing something here and that is why I ask....

No real reason, just curious, I need to get out the database mindset, I was just thinking when the nulls might 'appear' (they seem to be more like an EXCEPT clause that causes them, values in one 'table' but not the other

In your script... you are doing Len(Trim()) on AgentAction? instead or name_uq? I just want to clarify before we move forward...

I have commented out the trims and isnulls, the script is as is before we started to handle the nulls

staff (basically a personel table)

[name_uq]

this is the distinct staff name, to link the 2 different other tables by their login

Does that clarify things a bit?

• ###### Re: Assign a 'fixed' calculation to a column in table

I guess the problems is not nulls... but missing values for name_uq... it seems that there might be AgentLogin which are not associated with any mag_username.... what is the relation between name_uq and mag_username? may be you can use this in your denominator?

Sum(Aggr(

if

(

sum(Logged)>= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  > 0 //and at least one interaction

,sum(Logged)//then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders])  //or interactions as normal

),

• ###### Re: Assign a 'fixed' calculation to a column in table

Yes, there arent nulls in any of the source data going in

What you said is correct, there are some 'orphaned' values in either or both of the 2 fact tables if they can be called that.

Unfortunately I am not going to be able to test anymore until Monday now.  Your patience and understanding is very much appreciated, hope you have a great weekend

Does this make sense?

• ###### Re: Assign a 'fixed' calculation to a column in table

Sunny thanks for all your help last week.  I have marked this as the answer as it works when all the data is populated (as you were probably aware)

I didn't quite get it working with the pseudo nulls/orphaned records, I will come back to it...

However I fixed the issue which was causing them which is the better 'fix' for now, as the same data is to be used elsewhere