Assign a 'fixed' calculation to a column in table

[bha]

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!

[bha]

Zero in all rows

[sunny_talwar · Accepted solution]

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

[bha]

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)

[sunny_talwar]

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 .

[bha]

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

[sunny_talwar]

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?

[bha]

I have attached the data extracted from the latest solution

[bha]

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

[sunny_talwar]

What is this UnKnown? Is that null Agent?

[bha]

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