Date Manipulation

[akpofureenughwu]

Good day everyone,

Hope this meets you well.

I have a set of data that looks like this:

Repair:

Year	Month	Amount	Remarks
2016	January	10,000
2016	March	23,000
2016	September	40,000

TV Payment:

Year	Month	Amount	Remarks
2016		1000
2016	March	1500
2016	September	40,000

Supply

Year	Month	Amount	Remarks
2016	January	1
2016	March	23
2016	September	40

I need to develop a dashboard with data. I would love to develop a date format:   Year>Quarter> Month.. And I believe the primary key I should use is the year field.

Please share your thoughts on this

Regards

[cgdonders]

Hi akpofureenughwure111

Try this:

Supply:

LOAD

     Year,

     MakeDate(Year,Month(Date#(Month,'MMM')),1) AS Date, //1st date of month

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

     Amount

     FROM ....

     ;

based on the date field you can create more Calendar fields. Or use the Date field as a key to a Calendar table.

[akpofureenughwu]

Thanks Coen for your response..

I tried the scripts but I noticed there was an error message 'year' field not found.

Regards

Akpofure

[annafuksa1]

Use for example

load

Amount,

Remarks,

date(date#(Year&'/'&Month ,'YYYY/MMMM')) as Date,

'Q'&ceil( MOnth(date(date#(Year&'/'&Month ,'YYYY/MMMM')))/3) as Quarter,

Month(date#(Year&'/'&Month ,'YYYY/MMMM')) as Month,

Year(date#(Year&'/'&Month ,'YYYY/MMMM')) as Year

From Repair...;

for every table and I will recommend you create 1 fact table instead 3 tables .

Anna

[akpofureenughwu]

Thanks Anna,

Still Have some grey spots on your solution

[annafuksa1]

what do you mean ?

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

table1:
LOAD Year,
    Dual(Month,Month(Date#(Month,'MMMM'))) as Month,
    Amount,
    'Repair' as AmountType,
    Remarks
FROM [https://community.qlik.com/thread/251410] (html, codepage is 1252, embedded labels, table is @1);

LOAD Year,
    Dual(Month,Month(Date#(Month,'MMMM'))) as Month,
    Amount,
    'TV Payment' as AmountType,
    Remarks
FROM [https://community.qlik.com/thread/251410] (html, codepage is 1252, embedded labels, table is @2);

LOAD Year,
    Dual(Month,Month(Date#(Month,'MMMM'))) as Month,
    Amount,
    'Supply' as AmountType,
    Remarks
FROM [https://community.qlik.com/thread/251410] (html, codepage is 1252, embedded labels, table is @3);

Join
LOAD Distinct
    Year,
    Month,
    MonthName(MakeDate(Year,Month)) as MonthName,
    Dual('Q'&Ceil(Month/3),Ceil(Month/3)) as Quarter
Resident table1;

hope this helps

regards

Marco

[MarcoWedel]

Please close this thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco