topic Pivot table Grouping by Month not working in QlikView

Pivot table Grouping by Month not working

Sat, 07 Sep 2013 20:27:38 GMT

DEPT	YEAR	MONTH	SUM(INV_AMOUNT)	sum(Fre_Amount)
CDD	2012	Jan	3252433.17	0
CDD	2012	Feb	1478532.78	0
CDD	2012	Mar	1657229.35	0
CDD	2012	Jan	0	2950000
CDD	2012	Feb	0	1270000
CDD	2012	Mar	0	3750000

In Qlikview the first 3 rows in the above example - JAN,FEB,MAR are right alligned, the last 3 rows Left alligned.

First 3 records from database and last 3 records loaded from excel. while loading any truncate function available to make both values similar ?

Re: Pivot table Grouping by Month not working

kedar_dandekar — Sat, 07 Sep 2013 21:22:58 GMT

Hi Jayadevan,

Generally, in QlikView the content that is right aligned is interpreted as number,

whereas left aligned content is interpreted as text.

So the Months loaded from your database are interpreted as number, whereas the months being loaded from Excel are interpreted as text 'Oct', 'Nov'.. etc.

In the script, where you load the month column from Excel, try loading your month column using the following syntax:

Month(Date#(MONTH, 'MMM')) as MONTH

This basically does the string-to-numeric conversion. Check if this works, else

please attach your excel with sample data, this would help resolve the issue ..

Also, please find attached a Technical brief on 'QlikView Date Fields' for your reference.

HTH,

Re: Pivot table Grouping by Month not working

Sun, 08 Sep 2013 07:08:36 GMT

I tried Date#(Month,'MMM') as Month. Still it comes right aligned. I have given the command as follows :-

Date#(Month((INV_DATE),'MMM') as MONTH, //Jan, Feb

Thanks.

Re: Pivot table Grouping by Month not working

Jason_Michaelides — Sun, 08 Sep 2013 16:27:17 GMT

You've got it the wrong way round - KD suggested

Month(Date#(MONTH, 'MMM')) as MONTH

Re: Pivot table Grouping by Month not working

Mon, 09 Sep 2013 05:46:55 GMT

Thanks a lot.

Re: Pivot table Grouping by Month not working

qlikpahadi07 — Mon, 09 Sep 2013 06:19:19 GMT

Simply use Make date function from Back end this will resolve

Re: Pivot table Grouping by Month not working

kedar_dandekar — Mon, 09 Sep 2013 06:44:08 GMT

Hi Jayadevan, Also, ensure that you apply this script logic to the month column of your EXCEL load script and not your DB load script. This script logic assumes your month data in Excel is in text MMM format e.g. 'Jan','Feb'... Post load, the resulting format of your Excel Month field should then match with the DB Month format loaded. - KD

Re: Pivot table Grouping by Month not working

Mon, 09 Sep 2013 09:05:31 GMT

Thanks.

Re: Pivot table Grouping by Month not working

Jason_Michaelides — Mon, 09 Sep 2013 19:11:37 GMT

Please make sure you mark the correct answer to close the thread.