Solved: Re: Assign a 'fixed' calculation to a column in ta... - Page 2 - 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!

bha · ‎2018-08-02

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!

sunny_talwar · ‎2018-08-02

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 · ‎2018-08-02

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 · ‎2018-08-02

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

bha · ‎2018-08-02

Image1, all good

Image2 59.94 instead of 60.15

sunny_talwar · ‎2018-08-02

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 · ‎2018-08-02

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 · ‎2018-08-02

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

bha · ‎2018-08-02

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

sunny_talwar · ‎2018-08-02

Can you check if this works or not