topic Re: How to make dates as columns? in QlikView

How to make dates as columns?

Wed, 25 Nov 2020 16:16:04 GMT

Hi community.

I have been trying to figure out how to create this in Qlikview:

TOTAL RESPONSE
	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17
Analyst 1	31.58%	41.18%	75.00%	50.00%	70.59%	75.00%
Analyst 2	100.00%	100.00%	100.00%	70.00%	68.75%	76.92%
Analyst 3	58.82%	92.31%	90.00%	54.17%	53.57%	60.00%
Analyst 4	50.00%	66.67%	73.68%	68.75%	53.33%	75.00%
Analyst 5	80.00%	40.00%	68.97%	52.38%	69.57%	57.69%

I'm not having any luck in making the dates as columns. I'm sorry of this is too basic, but I would appreciate some guidance!

Re: How to make dates as columns?

sunny_talwar — Fri, 21 Jul 2017 18:51:30 GMT

Check this link out

The Crosstable Load

Re: How to make dates as columns?

Fri, 21 Jul 2017 19:15:00 GMT

Sunny,

Interesting, but a tad over my head! I'm still not sure of what to do after reading through it.

Re: How to make dates as columns?

sunny_talwar — Fri, 21 Jul 2017 19:19:50 GMT

The CrossTable Load syntax will help you convert your Month Year fields into a single Month field. if you can share you sample as an Excel file I can show how it can be done for you... but in general this is the idea

Table:

CrossTable (MonthYear, Data)

LOAD *

FROM .....;

and the second part of the script is used to help interpret the MonthYear field as a date field

Re: How to make dates as columns?

Fri, 21 Jul 2017 19:30:01 GMT

thanks, I've attached the file. As you will see, the table data is a calculation. I assume I would need to do that in my script, too?

Appreciate your help!

Re: How to make dates as columns?

sunny_talwar — Fri, 21 Jul 2017 19:35:41 GMT

John - I am confused... is the above is what you want to see as an output in a QlikView chart?? I was under the impression that you wanted to show the above table and convert it into QlikView data. I am working on it...

Re: How to make dates as columns?

Fri, 21 Jul 2017 19:39:05 GMT

Ha, sorry about that!

My goal is to display the above chart in my Qlikview file. I want to see the analyst and their performance for each given month as the column.

Re: How to make dates as columns?

sunny_talwar — Fri, 21 Jul 2017 19:47:22 GMT

Here check the attached

Re: How to make dates as columns?

Mon, 24 Jul 2017 13:15:05 GMT

Sunny - I've replicated your example in my qvw and it is not giving me the expected results; no months to columns and the %'s are not right (much too low). Any idea on what is going on? I appreciate your time in helping me!

Re: How to make dates as columns?

Nagaraju_KCS — Mon, 24 Jul 2017 15:04:31 GMT

John,

Your Excel Calculation,

IF(COUNTIFS(data!$A:$A,$B4,data!$B:$B,C$3)>0,(COUNTIFS(data!$A:$A,$B4,data!$G:$G,"LIVE",data!$K:$K,"< 10"

,data!$B:$B,C$3)+COUNTIFS(data!$A:$A,$B4,data!$G:$G,"ELEC",data!$K:$K,"< 60",data!$B:$B,C$3))/(COUNTIFS(data!$A:$A,$B4,data!$B:$B,C$3))," ")

This calculation based on 3 columns (NAME, SOURCE and RESPTIME)

Based on your excel..

(Count(LIVE < 10)

Count(ELEC < 60)

Count(NAME)

PFA,

Re: How to make dates as columns?

atoz1158 — Mon, 24 Jul 2017 16:33:39 GMT

John

To get the months as columns click and hold one of the month names and drag it above the % SLO column

Regards

Adrian

Re: How to make dates as columns?

Mon, 24 Jul 2017 16:40:27 GMT

Bingo! Problem solved...I knew it had to be something simple!!!