topic Re: count of no dates fall under week to week in App Development

count of no dates fall under week to week

Prasad1318 — Wed, 19 Oct 2022 12:21:04 GMT

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.

Re: count of no dates fall under week to week

ThiagoCN — Wed, 19 Oct 2022 13:17:36 GMT

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

Re: count of no dates fall under week to week

vinieme12 — Thu, 20 Oct 2022 03:48:53 GMT

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)

Re: count of no dates fall under week to week

Prasad1318 — Mon, 31 Oct 2022 12:38:16 GMT

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.