Changing the calendar format

[#1]

Hi:

We had a Qlikview developer come and help build a dashboard. Originally we were reporting information to the dashboard monthly and wanted to show our data information as follows:

"FiscalMonth - MM/YYYY"

Now we are making tweaks and reporting in a weekly format instead without the fiscal month. I came onto the project after the calendar was created, so I'm not sure where to make the changes to show the data in weekending format such as:

"MM/DD/YYYY"

Currently the information only shows in a date format if I load the data with fical month first.

Please help, a Qlikview file is attached, as well as the code in a Word Doc.

Thank you!

[bgerchikov]

Hi,

You should make changes in "Data Actuals" tab:

KPI:

NoConcatenate

LOAD

*,

DATE#(TRIM(left(mid(Date1,index( Date1, '/')-2),7)),'MM/DD/YYYY') as MonthYear, //clean up values removing trim and etc...

DATE#(TRIM(left(mid(Date1,index( Date1, '/')-2),7)),'MM/DD/YYYY') as Date,      // This field will be used in creating calendar

DATE#(TRIM(left(mid(Date1,index( Date1, '/')-2),7)),'MM/DD/YYYY')

;



LOAD Week as Week_Number,

     [Business Segment],

     [World Area],

     Product,

     [Brand Segment],

     [Package Type],

     [Sales Region],
...............................................
     [Week Ending] as Date1, 

...............................................

Just format the field above

date([Week Ending],'MM/DD/YYYY') as Date1,

Make sure this format consistent with Date1 in Calendar

Hope it will help

[#3]

Boris:

That did change how the date appears, but the actual data was not attached to the new date format. In the old format the data would show as long as I created the date as "1-09/01/2011". There is something in the code that defines the date to only pull with the fiscal month listed first. Any other suggestions?

[whiteline]

DATE# function converts string date value to numerical date value with default text representation (as it was in the string).

DATE function changes text representation.

You can use  DATE with desiered format over DATE# in script.

Or you can change date format for the whole document (Documen Properties -> Number, select your date field and change the format).

Or you can change date format using calculated dimension in charts instead of using your date field directly.

upd:

But your case is more complex because you use non standart date formatting with FiscalMonth.

Use =DUAL(Date1, DATE#(SubField(Date1, ' - ', 2), 'MM/DD/YYYY'))

This keeps Date1 as text representation and date as numeric value.