Solved: Re: Assign a 'fixed' calculation to a column in ta... - Qlik Community

bha · ‎2018-08-01

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!

sunny_talwar · ‎2018-08-02

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

View solution in original post

sunny_talwar · ‎2018-08-01

May be this

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

sunny_talwar · ‎2018-08-01

Read about What does the TOTAL qualifier do? here

bha · ‎2018-08-01

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.

bha · ‎2018-08-01

This might help!

sunny_talwar · ‎2018-08-01

Logged and Interactions are fields or expressions?

agigliotti · ‎2018-08-01

you could use the below expression:

sum(total Logged) / sum(total Interactions)

bha · ‎2018-08-01

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)

sunny_talwar · ‎2018-08-01

So, try something like this

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

bha · ‎2018-08-01

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

Assign a 'fixed' calculation to a column in table