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

[bvssudhakar]

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

[niclaz79]

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.

[jonathandienst]

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

[bvssudhakar]

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

[niclaz79 · Accepted solution]

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);

[bvssudhakar]

Hi Niclas,

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

Thank you in advance

[niclaz79]

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.

[bvssudhakar]

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

[niclaz79]

Hi,

Attach the qvf-file as an attachment.

[bvssudhakar]

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