How to customize the format in Pivot Visualization

[#1]

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.

[reddy-s]

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.

[#3]

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.

[#4]

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.

[reddy-s]

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;

[reddy-s]

Hi Kishore,

You need to create a master item with the max function, you cannot do it this way: Count(<Master Item>)

If your requirement is to colour the master items based on a condition use the colour by expression and you should be able to achieve what you need.