Sorting by week

[shamitshah]

Hi,

I have 2 bar charts -

1) Revenue by month (Fiscal Year July 2015 - June 2016)

2) Revenue by week

The  second bar chart by week does not align i.e. the week numbers (by calendar month) do not align to the the Fiscal year July 2015 to June 2016.

Is there any way to sort the week numbers so that they align to the Fiscal months?

Thanks

Shamit

[swuehl]

I would suggest that you create a master calendar with fiscal year, month, week etc. fields.

See for example

https://community.qlik.com/blogs/qlikviewdesignblog/2013/05/28/fiscal-year#comment-18082

The Master Calendar

Data Types in QlikView

This new week field should then be sorted numeric to align with your fiscal year.

[shamitshah]

Hi swuehl,

I have the following in the main script in the SALES table:

Date([SAL_InvoiceDate] + MakeDate(2000,1,1)) as SaleDate

I tried to follow step 3 in the example The Master Calendar above and ended up with the following to generate the dates:

Load Date([SAL_InvoiceDate] + MakeDate(2000,1,1)) as CalendarSaleDate;

Load Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as CalendarSaleMonth;

Load Week(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as CalendarSaleWeek;

Load Date(Min([SAL_InvoiceDate] + MakeDate(2000,1,1))+iterno()) as  CalendarSaleDate While iterno() <= Max(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) - Min(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) ;

Load Min(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))-1 as MinDate, Max(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as MaxDate resident SALES;

When I reload , I am getting the following error message:

Any idea where I am going wrong?

Thanks

Shamit

[swuehl · Accepted solution]

double check that SAL_InvoiceDate is part of your resident Sales table.

Why are you adding Makedate(2000,1,1) to both extrema?

Then, use MinDate and MaxDate in the second LOAD with the While loop instead of the aggregations.

Further, afte creating the CalendarSaleDate field, use this field in the calendar functions, not SAL_InvoiceDate.

You can read more about dates and calendars here:

How to use - Master-Calendar and Date-Values

[shamitshah]

Thanks swuehl,

I realised that I did not require the Makedate(2000,1,1) in both schema.

I also included the While loop in Temp Calendar to generate all the dates between the min and max.

All working now.

Thanks

Shamit