Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Thanks Coen for your response..
I tried the scripts but I noticed there was an error message 'year' field not found.
Regards
Akpofure
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
Thanks Anna,
Still Have some grey spots on your solution
what do you mean ?
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
Please close this thread if your question is answered:
Qlik Community Tip: Marking Replies as Correct or Helpful
thanks
regards
Marco