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: 
bha
Contributor III
Contributor III

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!

Capture.PNG

37 Replies
bha
Contributor III
Contributor III
Author

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

Cap1HappyDays.png

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!

Cap2Bad.png

sunny_talwar

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.

bha
Contributor III
Contributor III
Author

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

)

sunny_talwar

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

bha
Contributor III
Contributor III
Author

Image1, all good

Image2 59.94 instead of 60.15

sunny_talwar

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

bha
Contributor III
Contributor III
Author

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

sunny_talwar

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

bha
Contributor III
Contributor III
Author

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

sunny_talwar

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