Date format

[tan_chungkam]

Hi All,

Could Qliksense regonized the number 20181201 as  Year 2018 Month 12 and Week 1?

Is there any ways to do that?

[saminea_ANZ]

Perhaps this? But, why week as 1 here for this date?

='Year ' & Year(Date(Date#(DateField,'YYYYMMDD'))) & ' ' & 'Month ' & Num(Month(Date(Date#(DateField,'YYYYMMDD')))) & ' ' & 'Week ' & week(Date(Date#(DateField,'YYYYMMDD')))

[tan_chungkam]

Hi,

Thanks for the advice.

Not sure what your formula is working.

It doesn't work.

The reason why the week only contain 1 & 2 is the data is extracted on every 1st and 2nd week of each month.

[saminea_ANZ]

This will read as month, Year and Day but not the week.

Date(Date#(Field,'YYYYMMDD'),'YYYYMMDD')

[HirisH_V7]

Do use this:

Date:
LOAD *,
MonthName(Date#(left(Format, len(Format)-2),'YYYYMM'))& ' : Week '&Right(Format,2) as Date

INLINE [
Format
20181201
20181202
20181203
];

let me know if any issue.

[HirisH_V7]

How come we can define week number from date itself, as we are not having date in particular.

[HirisH_V7]

Even this:

'Year ' & Year(Date(Date#(Format,'YYYYMMDD'))) & ' ' & ' Month ' & Num(Month(Date(Date#(Format,'YYYYMMDD'))))& ' Week '&Right(Format,2) as Date

[tan_chungkam]

HI @HirisH_V7 ,

Thanks for your solution.

It work perfectly.

However I have another question.

It seem like it's just a data presentation instead of qliksense recognize it as a YearMonthWeek.

What if i would like to make a comparison between current Period (20190101 - Year 2019 Month January Week 01) and previous Period (20181202 - Year 2018 Month December Week 02).

Is that possible to do it?

[HirisH_V7]

Check out this, with some sample data:

Date:
LOAD *,
'Year ' & Year(Date(Date#(Format,'YYYYMMDD'))) & ' ' & ' Month ' & Num(Month(Date(Date#(Format,'YYYYMMDD'))))& ' Week '&Right(Format,2) as Date,
Year(Date(Date#(Format,'YYYYMMDD'))) as Year,
MonthName(Date(Date#(Format,'YYYYMMDD'))) as Month,
Right(Format,1) as Week
;
LOAD * INLINE [
    Format, Sales
    20181201, 2222
    20190101, 3333
        20181202, 22522
    20190102, 33353
        20181203, 22242
    20190103, 33433
];

using this as expressions in UI,

=Sum({<Month={"$(=MonthName(Max(Month)))"},Week={"$(=Max(Week))"}>} Sales)  //Current Period

=Sum({<Month={"$(=MonthName(AddMonths(Max(Month),-1)))"},Week={"$(=Max(Week))"}>} Sales) // Previous

We can achieve by using above, my question is like in previous period the week is getting changed i.e is +1. What if the max week in current  month period is 4 or 5. Let me know this. 

or else we can achieve same week comparison is two months, using above.

PFA For ref.  

[tan_chungkam]

Hi @HirisH_V7 ,

Thanks for your prompt reply.

I had tried your script.

For the current period it works fine. 

For the previous period it does not work correctly.

To answer your first question about the week, there is only 2 weeks in every month according to our data. 

For e.g., 20181201 (December 2018 Week 1) > 20181202 (December 2018 Week 2) > 20190101 (January 2019 Week 1).

For the comparison part, if the current period is 20190101, then the previous period is 20181202.

Another example, if the current period is 20181202 then the previous period is 20181201.

Hope that i answered your question.