0 Replies Latest reply: Oct 17, 2018 3:46 PM by Lori McClure RSS

    Qlik Sense treating number field as Boolean for Aggregates

    Lori McClure

      This data is set up so that there is 1 of three values selected per review, per question. Originally I tried to take care of this in expressions but was having trouble...so  I created a field for each value int he transformation like so and even went back and explicitly made these fields numeric:

      QRFieldValue:

      NoConcatenate

      Load

           [FieldValueId]

            ,[ValueName]

            ,num(if(ValueName = 'OK', 1, 0)) as OKCount

            ,num(if(ValueName = 'N/A',1, 0)) as NACount

            ,num(if(ValueName = 'NI',1, 0)) as NICount

            //   ,InsertDate

             From [$(vSourceQVD)$(vSourceQRA)QRFieldValue.qvd](qvd);

            Store QRFieldValue into [$(vTransformQVD)$(vSourceQRA)QRFieldValue.qvd](qvd); 

            Drop Table QRFieldValue;

       

       

       

      see below:  the top table shows the data at a review level and you can see the appropriate field has appropriate value given the value name.....but even though this is a numeric field and I am using a sum Aggregate, it seems to be treating it as Boolean (See Totals).  It does this anywhere I try to aggregate these fields....

      Has anyone seen this?

      Outside of creating a separate aggregate data set in the transformation, is there a way I can get it to treat these values as numbers and aggregate them appropriately?

      10-17-2018 2-32-15 PM.png10-17-2018 2-33-18 PM.png