Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Masters,
I am having trouble implementing my 13 period calendar into Qliksense. Could you help?
This is my master calendar:
YEAR | PERIOD | STARTDATE | ENDDATE | PAYPERIOD | PAYDATE | PAYSTART | PAYEND | No Days | Report Against |
2006 | 1 | 08/01/2006 | 04/02/2006 | 44 | 10/02/2006 | 08/01/2006 | 04/02/2006 | 28 | C |
2006 | 2 | 05/02/2006 | 04/03/2006 | 48 | 10/03/2006 | 05/02/2006 | 04/03/2006 | 28 | C |
2006 | 3 | 05/03/2006 | 31/03/2006 | 52 | 07/04/2006 | 05/03/2006 | 01/04/2006 | 27 | C |
2006 | 4 | 01/04/2006 | 29/04/2006 | 4 | 05/05/2006 | 02/04/2006 | 29/04/2006 | 29 | F1 |
2006 | 5 | 30/04/2006 | 27/05/2006 | 8 | 02/06/2006 | 30/04/2006 | 27/05/2006 | 28 | F1 |
2006 | 6 | 28/05/2006 | 24/06/2006 | 12 | 30/06/2006 | 28/05/2006 | 24/06/2006 | 28 | F1 |
2006 | 7 | 25/06/2006 | 22/07/2006 | 16 | 28/07/2006 | 25/06/2006 | 22/07/2006 | 28 | F2 |
2006 | 8 | 23/07/2006 | 19/08/2006 | 20 | 25/08/2006 | 23/07/2006 | 19/08/2006 | 28 | F2 |
2006 | 9 | 20/08/2006 | 16/09/2006 | 24 | 22/09/2006 | 20/08/2006 | 16/09/2006 | 28 | F2 |
2006 | 10 | 17/09/2006 | 14/10/2006 | 28 | 20/10/2006 | 17/09/2006 | 14/10/2006 | 28 | F3 |
2006 | 11 | 15/10/2006 | 11/11/2006 | 32 | 17/11/2006 | 15/10/2006 | 11/11/2006 | 28 | F3 |
2006 | 12 | 12/11/2006 | 09/12/2006 | 36 | 15/12/2006 | 12/11/2006 | 09/12/2006 | 28 | F3 |
2006 | 13 | 10/12/2006 | 06/01/2007 | 40 | 12/01/2007 | 10/12/2006 | 06/01/2007 | 28 | F3 |
And I want to link it to this data:
Form id | form submission id | date submitted | Are you reporting a Surplus or Loss, please select below | Amount of the anomaly incurred? |
200 | 116532 | 20/06/2019 18:28 | Loss | 7.75 |
200 | 116407 | 19/06/2019 10:27 | Loss | 2.88 |
200 | 116250 | 17/06/2019 23:46 | Surplus | 4.98 |
200 | 116248 | 17/06/2019 23:24 | Loss | 7.98 |
200 | 116224 | 17/06/2019 18:19 | Surplus | 4.65 |
This is just a snippet of each table.
Thanks,
It's not really a master-calendar but it could be used to create one, for example with:
cal: load *, date(STARTDATE + iterno() - 1) as Date from A while STARTDATE + iterno() - 1 <= ENDDATE;
and with:
date(floor([date submitted])) as Date
you creates a real date within your fact-table and both tables would be linked over this new field Date.
- Marcus
It's not really a master-calendar but it could be used to create one, for example with:
cal: load *, date(STARTDATE + iterno() - 1) as Date from A while STARTDATE + iterno() - 1 <= ENDDATE;
and with:
date(floor([date submitted])) as Date
you creates a real date within your fact-table and both tables would be linked over this new field Date.
- Marcus
Hi Marcus,
Thanks for getting in touch. where should I input these lines of code?
Sorry about the inexperience, I am new.
It's just:
NewMasterCalendar: load *, date(STARTDATE + iterno() - 1) as Date
from YourMasterCalendar while STARTDATE + iterno() - 1 <= ENDDATE;
Submission: load *, date(floor([date submitted])) as Date
from YourSubmissionTable;
- Marcus