count of no dates fall under week to week

[Prasad1318]

Hi all,
I am new to qlik,

I have a requirement where I need to build the Pivot table 
I have fields - CreatedDate, ClosureDate,
CreatedDate is connected to Master Calendar
Dimensions - week start - Master calendar Field week start,
                            week end - Master Calendar Field Week End,
Measures - Open - count of records when , CreatedDate field dates falls between WeekStart and Weekend

                       Closed - Count of Records when, ClosureDate Field Dates Falls between WeekStart and Weekend    
I tried logic aggr(Count(CreatedDate),WeekStart) for Open Measure    
but for Closed aggr(count(ClosureDate),WeekStart) this logic is not working.
Can someone help me on this ,

Thanks in Advance.      

[ThiagoCN]

Hello

Does your close Date have also his own master calendar?

because if want to use your field from the Master calendar assign to the Created it's not going to work for Closed, because one date we say is banana and another is apple, if try to make banana split with apple is not going to work.

You need create a master calendar for the Closed date too and use field from master calendar to the closed date then it will work.

Also, i could suggest you something like this, to not use aggr a simple flag in your script can make you user a set analysis because it will perform better than aggr.

In your script add this in your master calendar:

// In Master Calendar of Open

If(weekday(CreatedDate) > 4 , 1,0) As Created_WeekEnd_flag

// In Master Calendar of Closed

If(weekday(Closed ) > 4 , 1,0) As Closed_WeekEnd_flag

Then your expression will be

•  Count(  {< Created_WeekEnd_flag= {1} >}| CreatedDate) //Getting only Weekend Records
• Count(  {< Created_WeekEnd_flag = {0} >}| CreatedDate) //Getting only working days Records
•  Count(  {< Closed_WeekEnd_flag= {1} >}| ClosureDate) //Getting only Weekend Records
• Count(  {< Closed_WeekEnd_flag= {0} >}| ClosureDate) //Getting only working days Records

[vinieme12 · Accepted solution]

Create a bridge table so that both date fields are associated to the same calendar

Example

Maintable:
Load
Keyfield
,dim1
,dim2
,measure1
,measure2
,created_Date
,closure_Date
From Xyzdataset;

Bridge:
Load 
Keyfield
,created_Date as Date
,'created_date' as Datetype
Resident Maintable;
Concatenate(Bridge)
Load 
Keyfield
,closure_Date as Date
,'closure_date' as Datetype
Resident Maintable;

//MasterCalendar should link to Date field in Bridge table which has both closure and created dates now
MasterCalendar:
//add full calendar script here
AUTOGENERATE FieldValueCount('Date');

rfer below
https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

Now your expressions will be as below

Count of Created

=count({<Datetype={'created_date'}>} Dim1)

Count of Closed

=count({<Datetype={'closed_date'}>} Dim1)

[Prasad1318]

Apologies for late reply

Thank you for the solution Vinieme12

Got some additional problem  like 

Now week, count of created ,count of closed are columns in a table. I need to add one more column with calculation as follows

W1 = count of created-count of closed as (result1)

W2 = result1+count of created -count of closed as(result2)

W3 = result2+count of created-count of closed as(result3) and so on…

Please help me on this.

Thanks in advance.