topic Re: How to Generate Continuous Week Flags? in QlikView

How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 02:06:55 GMT

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‌

Re: How to Generate Continuous Week Flags?

uacg0009 — Thu, 31 Mar 2016 02:47:50 GMT

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

Re: How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 03:31:14 GMT

Hi,

Sample data attached.

Sub2u444

Re: How to Generate Continuous Week Flags?

uacg0009 — Thu, 31 Mar 2016 06:06:33 GMT

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 :

TicketID	StartDate	CloseDate	WeekNumber
101	04/07/2014 23:47:39	02/14/2016 15:00:00	15
102	06/08/2015 03:47:32	06/10/2015 17:24:00	24
103	08/18/2015 17:47:42	02/14/2016 08:00:00	34

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?

Kushal_Chawda — Thu, 31 Mar 2016 06:09:45 GMT

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

Re: How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 07:02:56 GMT

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.,

Sub2u444

Re: How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 07:05:48 GMT

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

Re: How to Generate Continuous Week Flags?

HirisH_V7 — Thu, 31 Mar 2016 07:16:28 GMT

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:

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

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

Hope this Helps,

PFA,

Hirish

Re: How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 08:12:52 GMT

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

Re: How to Generate Continuous Week Flags?

uacg0009 — Thu, 31 Mar 2016 08:56:00 GMT

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

Re: How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 09:14:10 GMT

Hi,

sorry to say, its not getting.

Sub2u444

Re: How to Generate Continuous Week Flags?

HirisH_V7 — Thu, 31 Mar 2016 09:18:05 GMT

As per your Data Provided i can't see any Data in between 15 and 24 Weeks.

Re: How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 09:27:01 GMT

Hi,

Yes, i need to show that gaps as 0

Ex:- WW16-0,WW17-0..WW23-0 like this

Re: How to Generate Continuous Week Flags?

uacg0009 — Thu, 31 Mar 2016 10:03:03 GMT

Hi,

Is that what you want?

If not please tell me.

Thanks.

Re: How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 10:31:43 GMT

Is it showing all weeks data with out gaps?

Re: How to Generate Continuous Week Flags?

HirisH_V7 — Thu, 31 Mar 2016 11:13:01 GMT

Hi,

All weeks data means as per the uacg0009‌ post the values are generated and joined to your table such that ,You can see only the week numbers and if Corresponding Data is there in your table means that will be displayed other wise it will be displayed as null().

Un checking the Suppress zero's in the presentation tab will show them.

else,

Please elaborate your requirement.

HTH,

Hirish

Re: How to Generate Continuous Week Flags?

Thu, 31 Mar 2016 11:27:57 GMT

Hi,

pls find the below sample data and Requirement snippet.

Filters:- Quarter & Week

** when you select Quarter it shows monthly data

** when you select Week it shows Particular week or its based on selection.

** Default-Latest 12 weeks data will display as attached in the snippet.

Thanks

Re: How to Generate Continuous Week Flags?

uacg0009 — Fri, 01 Apr 2016 03:47:38 GMT

How about this one?

Aiolos Zhao

Re: How to Generate Continuous Week Flags?

Fri, 01 Apr 2016 06:10:10 GMT

Hi,

I am using qlik sense. I am unable to open it.

can you share script and for expressions which your used the code..

Re: How to Generate Continuous Week Flags?

uacg0009 — Fri, 01 Apr 2016 06:56:24 GMT

Script:

Table1:
LOAD Text('WW'&IterNo()) as Start_Week_Number
AutoGenerate 1
While IterNo()<54;
Left Join

LOAD TicketID,
     Date(StartDate,'MM/DD/YYYY HH:MM:SS') AS StartDate,
     Date(CloseDate,'MM/DD/YYYY HH:MM:SS') AS CloseDate,
     if(left(Date(StartDate,'MM/DD/YYYY'),2)<4,'Q1',
      if(left(Date(StartDate,'MM/DD/YYYY'),2)<7,'Q2',
       if(left(Date(StartDate,'MM/DD/YYYY'),2)<10,'Q3','Q4'))) as StartDate_Quarter,
     left(Date(StartDate,'MM/DD/YYYY'),2) as StartDate_Month,
     Text('WW'&Week(StartDate)) as Start_Week_Number
FROM

(ooxml, embedded labels, table is Sheet1);

Table2:
NoConcatenate
LOAD *,
AutoNumber(Year(StartDate)&Week(StartDate)) as StartDate_Rank
// if(Week(StartDate)<>Peek(Week(StartDate)),rowno(),Peek(StartDate_Rank)) as StartDate_Rank

Resident Table1 Order by StartDate;

DROP Table Table1;

The first dimension :

=if(GetSelectedCount(Start_Week_Number)>0,Start_Week_Number,

aggr(if(rank(StartDate_Rank)<13,Start_Week_Number),StartDate_Rank))

Condition: if(GetSelectedCount(StartDate_Quarter)>0,0,1)

And suppress when value is null

The second dimension :

StartDate_Month

Condition: if(GetSelectedCount(StartDate_Quarter)>0,1,0)

2 expressions:

=Count(StartDate)

=Count(CloseDate)

Sort by Start_Week_Number using Expression - Descending : Expression = StartDate_Rank

Finally in Presentation tab, un-check the Suppress Zero-Values.

Thanks.