topic Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only in QlikView

Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

bvssudhakar — Tue, 20 Mar 2018 07:44:01 GMT

Hi All,

I am new to qlikview

I have only Year & Month Columns in excel file like this :

Year

Month

Bank Name

ATMs Onsite

ATMs Offsite

POS Online

POS Offline

CREDITCARD Transactions Through ATMs (Millions)

CREDITCARD Transactions Through POS (Millions)

DEBITCARD Transactions Through ATMs (Millions)

DEBITCARD Transactions Through POS (Millions)

Months are in string format like : January, February,...

Now my requirement is: how can i show the "Quarters", "Weeks", "Weekdays" in my qlikview file this is an urgent requirement any default thing is there can any one explain me briefly and how can i show present/latest Quarter details defaulty

And i am using Desktop addition

Thanks in Advance

Regards,

B V S Sudhakar

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

niclaz79 — Tue, 20 Mar 2018 08:01:58 GMT

Hi,

You need to create a master calendar based on your current dates. Suggestion is the following:

DataTable:

LOAD

MakeDate(Year, Month,'01') as %Date

Resident YourTable;

drop table YourTable;

drop fields Year, Month from DataTable;

Temp:

LOAD

Max(%Date) as MaxDate,

Min(%Date) as MinDate

Resident DataTable;

let vMaxDate = Floor(YearEnd(Peek('MaxDate',-1,'Temp')));

let vMinDate = Floor(YearStart(Peek('MinDate',-1,'Temp')));

drop table Temp;

Calendar:

Load

DateNum as %Date,

Month(DateNum) as Month,

Week(DateNum) as Week,

'Q' & Ceil(Month(DateNum)/3) as Quarter,

Year(DateNum) as Year;

Load

$(vMinDate) + IterNo() - 1 as DateNum

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

And now you should have a Calendar Table linking by %Date that includes Year, Month, Week and Quarter.

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

jonathandienst — Tue, 20 Mar 2018 08:07:07 GMT

What is the point including weeks and days if your source data only has years and months?

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

bvssudhakar — Tue, 20 Mar 2018 09:58:24 GMT

Hi Niclas,

Below is the my script :

MainTable:

LOAD Sr.No.,

Year,

Month,

[Bank Name],

[ATMs Onsite],

[ATMs Offsite],

[POS Online],

[POS Offline],

[CREDITCARD Transactions Through ATMs (Millions)],

[CREDITCARD Transactions Through POS (Millions)],

[DEBITCARD Transactions Through ATMs (Millions)],

[DEBITCARD Transactions Through POS (Millions)]

FROM

(ooxml, embedded labels);

Now can you modify what you gave the script for this because i am confusing when loading time it is showing error. may be i think naming is the problem

Where should i change the name in that script

My Excel Sheet Name is:ATM & POS Statstics For 2016 & 2017

Don't mind, Please help me out from this

Thank you in advance

B V S Sudhakar

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

niclaz79 — Tue, 20 Mar 2018 10:02:04 GMT

Hi, try this.

MainTable:

LOAD Sr.No.,

Year,

Month,

[Bank Name],

[ATMs Onsite],

[ATMs Offsite],

[POS Online],

[POS Offline],

[CREDITCARD Transactions Through ATMs (Millions)],

[CREDITCARD Transactions Through POS (Millions)],

[DEBITCARD Transactions Through ATMs (Millions)],

[DEBITCARD Transactions Through POS (Millions)],

MakeDate(Year, Month,'01') as %Date

FROM

(ooxml, embedded labels);

drop fields Year, Month from MainTable;

Temp:

LOAD

Max(%Date) as MaxDate,

Min(%Date) as MinDate

Resident MainTable;

let vMaxDate = Floor(YearEnd(Peek('MaxDate',-1,'Temp')));

let vMinDate = Floor(YearStart(Peek('MinDate',-1,'Temp')));

drop table Temp;

Calendar:

Load

DateNum as %Date,

Month(DateNum) as Month,

Week(DateNum) as Week,

'Q' & Ceil(Month(DateNum)/3) as Quarter,

Year(DateNum) as Year;

Load

$(vMinDate) + IterNo() - 1 as DateNum

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

bvssudhakar — Tue, 20 Mar 2018 10:16:09 GMT

Hi Niclas,

I tried this script but i got these errors one by one....... can you check once please

Thank you in advance

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

niclaz79 — Tue, 20 Mar 2018 10:22:19 GMT

I made a small adjustment to the script. Please try again. The error you see was a naming issue.

If you still have errors, please attach the application and I will fix it. It's very hard to trouble-shoot like this.

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

bvssudhakar — Tue, 20 Mar 2018 10:42:57 GMT

I also modified but errors are coming,

I am using Desktop Edition and can you tell me how to attach the application to you in this thread

Thank you in advance

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

niclaz79 — Tue, 20 Mar 2018 10:45:03 GMT

Hi,

Attach the qvf-file as an attachment.

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

bvssudhakar — Tue, 20 Mar 2018 10:54:23 GMT

how to attach the file, here i am not able to see the insert attachment option

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

sunny_talwar — Tue, 20 Mar 2018 11:38:50 GMT

Uploading a Sample

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

kakani87 — Tue, 20 Mar 2018 12:11:15 GMT

you can attach a file or document by selecting the use advanced editor option on right top

See the screen for reference

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

Chanty4u — Wed, 21 Mar 2018 10:32:07 GMT

open your thread in new window then you can able to see the option in reply with use advanced editor option

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

bvssudhakar — Wed, 21 Mar 2018 11:13:25 GMT

Hi Chanty

Thank you

and how to close the thread

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

sunny_talwar — Wed, 21 Mar 2018 11:14:24 GMT

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Master Calendar for Quarter, Week & Weekdays with year & Month Columns Only

bvssudhakar — Wed, 21 Mar 2018 11:27:38 GMT

Hi Niclas,

Thank you very much for giving the script. It is working perfectly

and one more small query if we have date column how can we write the script.