5 Replies Latest reply: Jun 21, 2017 4:23 AM by Avinash Jain RSS

    Qlikview Histogram with Multiple Dimensions Values

    Avinash Jain

      Dear Team,

       

      We are getting Handset Model wise RSCP ranges in a day where with distribution of -5DB as header and its correpsonfing count values.

      With this multiple columns for RSCP how can we show Histogram per range of rscp.

      Sample data looks like as below:

         

      Model-115-110-105-100-95-90-85-80-75-70-65-60-55-50-45-40-35-30
      Motorola-Motorola Moto G 2nd Gen342238501276058932183263115981139658281332
      Samsung-Samsung Galaxy Grand 231122125444100921781840116473212615511162
      Microsoft-Microsoft Lumia 535 3102023574117851633140586376194167024883161

       

       

      How to achieve different range value sin X AXES for multiple value like as above.

      Output in Bar chart or Line charr as below for different Models.

      Can you please advise  and prvide your valuable inputs to achive Hisotgrams charts?

      Sample data attached.

        • Re: Qlikview Histogram with Multiple Dimensions Values
          Stefan Wühl

          Use a CROSSTABLE LOAD prefix to transform your input:

          The Crosstable Load

           

          SET ThousandSep=',';
          SET DecimalSep='.';
          
          INPUT:
          CrossTable(RSCP, Count)
          LOAD Model,
              [-115.000000],
              [-110.000000],
              [-105.000000],
              [-100.000000],
              [-95.000000],
              [-90.000000],
              [-85.000000],
              [-80.000000],
              [-75.000000],
              [-70.000000],
              [-65.000000],
              [-60.000000],
              [-55.000000],
              [-50.000000],
              [-45.000000],
              [-40.000000],
              [-35.000000],
              [-30.000000]
          FROM
          [C:\Users\Stefan\Downloads\Device Experience Sheet_RSCP Distribution.xlsx]
          (ooxml, embedded labels, table is [RSCP Distribution], filters(
          Remove(Col, Pos(Top, 20))
          ));
          
          
          FACTS:
          NoConcatenate
          LOAD Model, Round(Num#(RSCP)) as RSCP, Count
          Resident INPUT;
          
          
          DROP TABLE INPUT;
          

           

           

          Then create a line chart with dimensions Model and RSCP and Sum(Count) as expression:

           

          2017-06-14 12_26_44-Device Experience Sheet_RSCP Distribution.xlsx  [Geschützte Ansicht] - Excel.png

            • Re: Qlikview Histogram with Multiple Dimensions Values
              Avinash Jain

              Hi Stefan,

              Thanks  a lot for your quick advise on this .

              Get to know from this how Crossable works perfect in such scenarios.

              • Re: Qlikview Histogram with Multiple Dimensions Values
                Avinash Jain

                Dear Stefan,

                Can you please advise on the  below after using the Crosstable.?

                 

                What we need is to calculate Percent contribution of each model base don threshold set by user in Straight table or Pivot table

                For example if user sets Thresold =-95dbm then we need to calculate (sum of all columns values  <=-95)/D_RSCP /

                Threshold is set by user base don the column header that we have .(-130 to >-25DB).

                As shown below Average= X_RSCP/D_RSCP .

                But Percent=  (sum of all columns values <=-95)/D_RSCP)

                So here for Asus Model it result shown as =4/4865.

                I am not able to identify how to get the dynamic sum of  coulmns below the threshold set ie( <=-130 + <=-125..... +<=-95) at run time.

                I was trying with RANGESUM but could not suuceed in getting the below result.

                Can you pleas eprovide your vaualbe imputs here.

                Sample data provided with Output.

                   

                Unit: Count, Sort By: Average, Threshold = -95
                ModelPercentAverageX_RscpD_Rscp<=-130<=-125<=-120<=-115<=-110<=-105<=-100<=-95<=-90<=-85<=-80<=-75<=-70<=-65<=-60<=-55<=-50<=-45<=-40<=-35<=-30<=-25>-25
                Asus-Asus Zenfone 50.08-82.97-40366548650000000413664920041582293117293615000000
                Microsoft-Microsoft Mobile Lumia 640 XL Dual SIM/RM-10670.07-80.61-217815270200000002172457459795141603360100000
                Samsung-Samsung Galaxy E70.05-79.81-3352954201000000025842011221415364537271102000000
                Xiaomi Corporation-Xiaomi 2014818/H2X LTE/Xiaomi Redmi 2 LTE1.38-79.13-356385450400001175335175058672277261541222212000000