Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to Generate Continuous Week Flags?

Hi Folks,

I have 2 date fields like start date and close date.I got the week flags with the help of Start Date.But here getting some gaps here.

Ex:- Week - WW1,WW2,....WW15,WW24......

i want to generate the flags in between 15 to 24..

what is the best method..

Sub2u444

19 Replies
uacg0009
Valued Contributor

Re: How to Generate Continuous Week Flags?

Hi SubbaReddy,

Could you please give some sample data so I can know what you want exactly?

Or you can show the result that you want in excel?

Thanks.

Aiolos Zhao

Not applicable

Re: How to Generate Continuous Week Flags?

Hi,

Sample data attached.

Sub2u444

uacg0009
Valued Contributor

Re: How to Generate Continuous Week Flags?

Hi,

I have got your sample data, but could you please tell me what kind of result you want?

For you sample data, for exapmle for the first 3 lines :

     

TicketIDStartDateCloseDateWeekNumber
10104/07/2014 23:47:3902/14/2016 15:00:0015
10206/08/2015 03:47:3206/10/2015 17:24:0024
10308/18/2015 17:47:4202/14/2016 08:00:0034

Do you mean you only want the WeekNumber of line 1 and line 2?

Or could you please show me what you want?

Thanks.

Re: How to Generate Continuous Week Flags?

May be you have created the Week flag on StartDate in which some dates are missing. Create the Calender using startdate with all the Dates between min Start Date & Max of StartDate and then create the Flag in calendar itself

Not applicable

Re: How to Generate Continuous Week Flags?

Hi,

Please find the attached snippet.

In the below image it was showing some gaps in Quarter and Week Flags. I want to avoid those gaps.

Ex:- ww15,ww24

i need to show ww15,ww16,ww17,ww18,ww19....ww24...etc.,

Sub2u444Pic.JPG

Not applicable

Re: How to Generate Continuous Week Flags?

Hi,

I created Master Calendar, but here when i select a Quarter/Month. It was not showing the data.

I already share the sample data and required format attached in the above.

Regards,

Sub2u444

hirishv7
Honored Contributor

Re: How to Generate Continuous Week Flags?

Hi,

I hope like this,

Two ways by creating a flag at backend or Set analysis,

Date:

LOAD TicketID,

    Timestamp(StartDate,'DD/MM/YYYY h:mm:ss[.fff] TT') as StartDate,

    Timestamp(CloseDate,'DD/MM/YYYY h:mm:ss[.fff] TT') as CloseDate,

    Week(Timestamp(StartDate,'DD/MM/YYYY h:mm:ss[.fff] TT')) as WeekStart,

    If(Week(Timestamp(StartDate,'DD/MM/YYYY h:mm:ss[.fff] TT'))>= 15 and Week(Timestamp(StartDate,'DD/MM/YYYY h:mm:ss[.fff] TT'))<= '24', 1)as [15-24 Flag]

FROM

[Sample (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

At front end:

Week Start date and Close Date Flag.PNG

Only({<[15-24 Flag]={'1'}>}WeekStart)

Only({<WeekStart={'>=15<=24'}>}WeekStart)

Hope this Helps,

PFA,

Hirish

Not applicable

Re: How to Generate Continuous Week Flags?

Hi,

Thanks for the reply.

In the above attached snippet in WeekFlag has some gap in between  WW15 & WW24. I need to fill those like WW15,WW16,WW17....WW24.

I applied master calendar, but here is not showing data.

Sub2u444

uacg0009
Valued Contributor

Re: How to Generate Continuous Week Flags?

Hi,

So, that means you need to create the gap week into your data.

The first idea in my mind is also that you need to create a table with a column named weeknumber, the value is from WW1 to WW53, maybe.

And then you need to do the left join to make your data to left join to this table.

Then you have a table with ALL weeknumber and you dates.

Then create a chart like above and do some limit to show 15-24.

If there is any problem please tell me, sorry i'm little busy so I don't create an example for you.

Thanks.

Aiolos Zhao

Community Browser