How to Generate Continuous Week Flags?

[#1]

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‌

[#2]

Hi,

sorry to say, its not getting.

Sub2u444

[HirisH_V7]

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

[#4]

Hi,

Yes, i need to show that gaps as 0

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

[uacg0009]

Hi,

Is that what you want?

If not please tell me.

Thanks.

[#6]

Is it showing all weeks data with out gaps?

[HirisH_V7]

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

[#8]

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

[uacg0009]

How about this one?

Aiolos Zhao

[#10]

Hi,

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

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

[uacg0009]

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.