Pivot table Grouping by Month not working

[#1]

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 ?

[kedar_dandekar]

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,

KD

[#3]

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.

[Jason_Michaelides]

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

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

[#5]

Thanks a lot.

[qlikpahadi07]

Simply use Make date function from Back end this will resolve

[kedar_dandekar]

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

[#8]

Thanks.

[Jason_Michaelides]

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