4 Replies Latest reply: Jun 3, 2015 11:07 AM by Satish Pawar RSS

    Set analysis

    Satish Pawar

      Hi,

       

      I'm working on Qlik sense.

       

      I have four column

      a) created_date

      b) param_name

      c) param_value

      d) branch_type

       

      another table

      a) branch_type

      b) Green

      c) Amber

      d) branch_type

       

      i want set analysis for below tables like this:

       

      sum(Temperature) for branch_type=DEPOT(not for Humidity & vehicle) / count (created_date)  for branch_type=DEPOT in terms of same rows of temperature.

      (Same for PT)


      from this

      Step 1--> i got the Avg Temperature

      Step 2--> To this Avg temperature. i decide to it that in which category it belongs ( Green, Amber, Red)

                     divide it by avg of Green, Amber, Red (Predefined Below) and multiplied by 100 to get '% Temperature Compliance'.

       

      Green--> -25,-26,-27,-28...onward-----------Avg of Green  = -25

      Amber--> -23,-24-------------------------Avg of Amber = -23.5

      Red--> -22,-21,-20...onward--------------------Avg of Red    =  -22

       

      Suppose i got the Avg Temperature (Step 1) is -23.4.

       

      then Now it belongs in Amber so-----> (-23.4/-23.5)*100= 99.57 %

       

      (Avg Temperature/ Avg of (Green/Amber/Red))*100

       

      i had tried it many way but it won't achievable for me.

      please give me solution.

       

       

      created_dateparam_nameparam_valuebranch_type
      01/02/2015Humidity20DEPOT
      01/03/2015Temperature-24DEPOT
      01/04/2015Temperature-21DEPOT
      01/05/2015Temperature-22PT
      01/06/2015Temperature-23DEPOT
      01/07/2015Temperature-20DEPOT
      01/08/2015vehicleMHDEPOT
      01/09/2015Temperature-19PT
      01/09/2015Temperature-18PT
      01/09/2015Temperature-20PT
      branch_typeGreenAmberYellow
      DEPOT-25 and Below-25 to -22-22 and above
      PT-25 and Below-25 to -22-22 and above

      mtophiliphandNew to Qlik Sensejpeanbu1984New to QlikView

       

      Message was edited by: Satish Pawar