Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
tan_chungkam
Contributor

Date format

Hi All,

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

Is there any ways to do that?image.png

Labels (3)
1 Solution

Accepted Solutions
HirisH_V7
Honored Contributor

Re: Date format

Hello I have revamped some code it will  be straight. 

Hope it serves u r purpose well.

 

Date:
LOAD * INLINE [
    Format, Sales
    20181201, 2222
    20181202, 22522
    20190101, 3333
    20190102, 33353
    20190102, 545544
];

Sort:
Load distinct Format,AutoNumber(Format) as Sort
Resident Date Order by Format;

 

 

Current:

Sum({<Sort={"$(=(Max(Sort)))"}>} Sales)

Previous:

Sum({1<Sort={"$(=(Max(Sort)-1))"}>} Sales)

 

 

PFA For ref.

HirisH
“Aspire to Inspire before we Expire!”
14 Replies
saminea_ANZ
Contributor

Re: Date format

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
Contributor

Re: Date format

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
Contributor

Re: Date format

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

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

HirisH_V7
Honored Contributor

Re: Date format

Do use this:

 

Capture.JPG

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
“Aspire to Inspire before we Expire!”
HirisH_V7
Honored Contributor

Re: Date format

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

HirisH
“Aspire to Inspire before we Expire!”
HirisH_V7
Honored Contributor

Re: Date format

Even this:

'Year ' & Year(Date(Date#(Format,'YYYYMMDD'))) & ' ' & ' Month ' & Num(Month(Date(Date#(Format,'YYYYMMDD'))))& ' Week '&Right(Format,2) as Date
HirisH
“Aspire to Inspire before we Expire!”
tan_chungkam
Contributor

Re: Date format

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?

Highlighted
HirisH_V7
Honored Contributor

Re: Date format

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.  

 

HirisH
“Aspire to Inspire before we Expire!”
tan_chungkam
Contributor

Re: Date format

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.