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

    Assign a 'fixed' calculation to a column in table

    Matt Wilson

      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

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

          May be this

           

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

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

            you could use the below expression:

            sum(total Logged) / sum(total Interactions)

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

              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
                Matt Wilson

                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

                  • Re: Assign a 'fixed' calculation to a column in table
                    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.

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

                        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
                            Sunny Talwar

                            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
                                Matt Wilson

                                Image1, all good

                                 

                                Image2 59.94 instead of 60.15

                                  • Re: Assign a 'fixed' calculation to a column in table
                                    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))

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

                                        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
                                            Sunny Talwar

                                            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
                                                Matt Wilson

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

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

                                                          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
                                                              Matt Wilson

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

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

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

                                                                        • Re: Assign a 'fixed' calculation to a column in table
                                                                          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?

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

                                                                            I have attached the data extracted from the latest solution

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

                                                                                What is this UnKnown? Is that null Agent?

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

                                                                                    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
                                                                                        Sunny Talwar

                                                                                        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
                                                                                            Matt Wilson

                                                                                            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
                                                                                              Matt Wilson

                                                                                              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]:

                                                                                              LOAD

                                                                                              [AgentLogin],

                                                                                              //If(Len(Trim(AgentLogin)) = 0, ' ', AgentLogin) as AgentLogin,    //or here hmmmmmmmmm does it go here

                                                                                              //if(IsNUll([AgentLogin]), 'TEST', [AgentLogin]) as [AgentLogin]  , //or would isnull/work be neater?

                                                                                              Date([avaDate]) AS [ava_HandledByAgent.avaDate],

                                                                                              [Voice] AS [ava_HandledByAgent.Voice],

                                                                                              [Orders] AS [ava_HandledByAgent.Orders],

                                                                                              [Customer_Service] AS [ava_HandledByAgent.Customer_Service],

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

                                                                                              [avaDate]&[AgentLogin] AS [ava_HandledByAgent.CallDate-avaDate],

                                                                                              [avaDate]&[AgentLogin] AS [CallDate-avaDate];

                                                                                              SQL SELECT "AgentLogin",

                                                                                              "avaDate",

                                                                                              "Voice",

                                                                                              "Orders",

                                                                                              "Customer_Service",

                                                                                              "Non-Voice"

                                                                                              FROM "ava_HandledByAgent";


                                                                                              [vLinkTable]:

                                                                                              LOAD

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

                                                                                                  [AvaAgentLogin], //and this one too which links to ava_HandledByAgent

                                                                                                  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],

                                                                                              [CallDate]&[AvaAgentLogin] AS [vLinkTable.CallDate-avaDate],

                                                                                              [CallDate]&[AvaAgentLogin] AS [CallDate-avaDate],

                                                                                              [CallDate]&[magusername] AS [CallDate-magDate],

                                                                                                  exclude,

                                                                                                  Notes

                                                                                                  ;

                                                                                              SQL SELECT "magusername",

                                                                                              "AvaAgentLogin",

                                                                                              "name_uq",

                                                                                              "CallDate",

                                                                                              "reportstoname",

                                                                                                  "exclude",

                                                                                                  "Notes"

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


                                                                                              [mag_LoggedbyAgent]:

                                                                                              LOAD

                                                                                              [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]&[username] AS [CallDate-magDate];

                                                                                              SQL SELECT "username",

                                                                                              "magDate",

                                                                                              "Logged"

                                                                                              FROM "mag_LoggedbyAgent";

                                                                                               


                                                                                              Capture.PNG@dunn

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

                                                                                                  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
                                                                                                      Matt Wilson

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

                                                                                                       

                                                                                                      To you mean AgentLogin?

                                                                                                      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

                                                                                                      [mag_username].  This links to [username]  in [mag_LoggedbyAgent]

                                                                                                      [AgentLogin].  This links to [Login] in [ava_HandledByAgent]


                                                                                                      Does that clarify things a bit?


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

                                                                                                          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

                                                                                                          ),

                                                                                                          AgentLogin))

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

                                                                                                              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?

                                                                                                               

                                                                                                              Capture.PNG

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

                                                                                          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

                                                                                           

                                                                                          Your help much appreciated!

                                                                                           

                                                                                          All the best

                                                                                           

                                                                                          (I do believe I have one more thread to come back on you commented on, regarding filtering on an expression), I will get back onto that as soon as I have a moment