Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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
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)
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.