topic Re: Months with No Date in QlikView

Months with No Date

tmumaw — Thu, 23 Mar 2017 11:13:45 GMT

Good morning everyone,

I have this table which has monthly buckets for debits, credits and year. The different buckets represent the months. Something like this:

C01 C02 C03 C04 C05 C06 C07 C08 C09 C10 C11 C12 D01 D02 D03 D04 D05 D06 D07 D08 D09 D10 D11 D12 YEAR

My report need to be formatted something like this:

Jan Credit Debit Bal

Feb.......

Mar.......

Etc......

How can I get each bucket to represent a month ?

Thanks

sunny_talwar — Thu, 23 Mar 2017 11:30:47 GMT

May be this

Month(Date#(PurgeChar(FieldName, 'CD'), 'M')) as Month

tmumaw — Thu, 23 Mar 2017 11:40:18 GMT

Each one of those fields at the top are months

C01 jan

D01 jan

C02 Feb

D02 Feb

sunny_talwar — Thu, 23 Mar 2017 11:41:43 GMT

Yes, I get that.... and I am guessing your data looks like this

FieldName

C01

D01

C02

D02

C03

Right?

tmumaw — Thu, 23 Mar 2017 11:46:25 GMT

No. My data looks like this. One record contains all months.

C01 C02 C03 C04 C05 C06 C07 C08 C09 C10 C11 C12 D01 D02 D03 D04 D05 D06 D07 D08 D09 D10 D11 D12 YEAR

sunny_talwar — Thu, 23 Mar 2017 11:48:10 GMT

Then may be you need to use The Crosstable Load to transform your data so that C01 and D01 can be changed to Jan and so on....

shraddha_g — Thu, 23 Mar 2017 12:20:45 GMT

date(date#(right(Fieldname,2),'MM')) as MonthStart

Monthname(date(date#(right(Fieldname,2),'MM'))) as Month-Year,

Month(date(date#(right(Fieldname,2),'MM'))) as Month