5 Replies Latest reply: Jan 15, 2016 4:18 AM by Sangram Reddy RSS

    How to customize the format in Pivot Visualization

    Kishore Kumar

      Hello Team,

       

      Need your input on how to achieve the format (mentioned in enclosed image) in Qlik Sense for pivot visualization.

       

      Red Boxes: Static Headings

      Blue boxes: Dynamic values coming from a column.

       

      Also, please clarify below questions,

      1) Can we show all the data in image in one pivot table. if yes, please tell me how, especially dynamic values as headings.

      2) How to show a Trendline under last column.

      3) I want to add Region as a filter above pivot table to filter data. I tried drag n drop but its not filtering correctly.

       

      Your support is highly appreciated.

       


        • Re: How to customize the format in Pivot Visualization
          Sangram Reddy

          Hi Kishore,

           

          Firstly its totally possible to display the data as in the image using a Pivot table in Qliksense. Infant it would look more intuitive. Add a row with region and 2 columns with Category1 and Category 2. Now add all your measures. This will create a pivot table as shown in the image.

           

          Now answering your Questions:

          1) The data can be shown that way, but you cannot have dynamic values in the headings yet.

          2) Are you looking for a mini chart in the pivot table? If so thats not possible using a pivot table. I would suggest you to go for an extension if its that important.

          3) You can have as many filters as possible.

           

          Thanks and Regards,

          Sangram Reddy.

            • Re: How to customize the format in Pivot Visualization
              Kishore Kumar

              Thanks for your response Sangram. I will try and get back to you if I have further questions.

               

              Quick question: Do you know how to get quarter and year concatenated. For ex: Q12013. I have a date in the script. I am ok to calculate this in script or in expression.

                • Re: How to customize the format in Pivot Visualization
                  Sangram Reddy

                  Hi Kishore use this script:

                   

                  QuartersMap: 

                  MAPPING LOAD  

                  rowno() as Month, 

                  'Q' & Ceil (rowno()/3) as Quarter 

                  AUTOGENERATE (12); 

                   

                   

                  Let varMinDate = Num('01/01/1950'); 

                  Let varMaxDate = Num('31/12/2050');

                   

                  TempCalendar: 

                  LOAD 

                                 $(varMinDate) + Iterno()-1 As Num, 

                                 Date($(varMinDate) + IterNo() - 1) as TempDate 

                                 AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                   

                  [Master Calendar]: 

                  Load *,

                        [Quarter]&[Year];

                  Load 

                                 TempDate AS [Date], 

                                 week(TempDate) As [Week], 

                                 Year(TempDate) As [Year], 

                                 Month(TempDate) As [Month], 

                                 Day(TempDate) As [Day], 

                                 YeartoDate(TempDate)*-1 as [CurYTDFlag], 

                                 YeartoDate(TempDate,-1)*-1 as [LastYTDFlag], 

                                 inyear(TempDate, Monthstart($(varMaxDate)),-1) as [RC12], 

                                 date(monthstart(TempDate), 'MMM-YYYY') as [MonthYear], 

                                 ApplyMap('QuartersMap', month(TempDate), Null()) as [Quarter], 

                                 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [WeekYear], 

                                 WeekDay(TempDate) as [WeekDay] 

                  Resident TempCalendar 

                  Order By TempDate ASC;

                   

                   

                  store [Master Calendar] into [lib://WOC Debts Extracts/master_calander.qvd];

                   

                   

                  Drop Table TempCalendar;

              • Re: How to customize the format in Pivot Visualization
                Kishore Kumar

                Quick question: Can anybody tell me how to find the Max value of a column (which is part of Master item).

                 

                firstly, I am not sure how to put aggregation function for master item.

                secondly, the master item already has a aggregation function i.e. count(A), A is a column. Count(A) will bring me 1, 2, 3, 4 values in a column.

                I want to put Red color to Min value and Green color to Max value. How do I achieve this. please advise.