Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have below data in back end :-
Data Fields |
Issue Recd Month |
Issue closed Month |
Volumes |
Issue no Report Date |
And I wish to create a table in front end like below :-
KPI's | Jan-22 | Feb-22 | Mar-22 |
Total Closure | |||
Total Received | |||
Total Volumes |
I have below problem:-
1) I don't have fields named as Total Closure , Total received, total volumes in backend table .
Hence, I created dummy data using Inline table
2) Show numbers against same date field.
The biggest problem here is the calculation. Below logic is used to calculate these KPI's -
- Total Closure
Dimension -Issue closed Month(Date Format -MMM-YY)) | Measure - count(issue no)
-Total Received
Dimension -Issue Recd Month (MMM-YY) | Measure - count(issue no)
-Volumes
Dimension -Report Date(MMM-YY) | Measure - Sum(Volumes)
Now to calculate each KPI, different set of date is used to populate values. Is there a way I can show all the three KPI calculation using one single date dimension in pivot table?
Can anyone please help me on this ?
Thanks
Hi, just follow the bridge code and adapt to your fields, like:
DateBridge:
Load
[Issue No],
ReceivedDate as CanonicalDate,
'Received' as DateType
Resident DataTable;
Concatenate (DateBridge)
Load
[Issue No],
ClosedDate as CanonicalDate,
'Closed' as DateType
Resident DataTable;
Concatenate (DateBridge)
Load
[Issue No],
ReportDate as CanonicalDate,
'Report' as DateType
Resident DataTable;
The expressions could be
Closure: Count( {$<DateType={'Closed'}>} [Issue No])
Received: Count( {$<DateType={'Received'}>} [Issue No])
And for volume pick the date you want, like Sum({$<DateType={'Report'}>} Volumes) if you want to volumes based on the report date
Hi, yes, if it's left-aligned it means it's loaded as a string, and date functions won't work.
You can use Date#() to tell the input format when loading the date, like:
Date(Date#(ISSUE_CLOSED_DATE,'DD-MMM-YYYY'))
You need to first use Date#() to convert the string to date, then you can convert to number or another date format:
Num(Date#(ISSUE_CLOSED_DATE,'DD-MMM-YYYY'))
Hi, you can try a solution like the data bridge used in the canonical date: https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578
Hi, Thanks for your response.
I am not very much clear on how to proceed with this. I had already seen this post from Henric.
But not able to relate it with my scenario properly.
Hi, just follow the bridge code and adapt to your fields, like:
DateBridge:
Load
[Issue No],
ReceivedDate as CanonicalDate,
'Received' as DateType
Resident DataTable;
Concatenate (DateBridge)
Load
[Issue No],
ClosedDate as CanonicalDate,
'Closed' as DateType
Resident DataTable;
Concatenate (DateBridge)
Load
[Issue No],
ReportDate as CanonicalDate,
'Report' as DateType
Resident DataTable;
The expressions could be
Closure: Count( {$<DateType={'Closed'}>} [Issue No])
Received: Count( {$<DateType={'Received'}>} [Issue No])
And for volume pick the date you want, like Sum({$<DateType={'Report'}>} Volumes) if you want to volumes based on the report date
Hi @rubenmarin , Thanks for your response .
I tried the above logic . Created three master calendars - issue recd date , issue closed date, report date . And one calendar for canonical date. Please see below code :-
Data:
LOAD
REPORT_DATE,
F1,
LOB,
MEDIUM,
ISSUE_TYPE,
ISSUE_NO,
SUMMARY,
CUSTOMER_NAME,
ACCOUNT_NUMBER,
PROCESSED_BY,
REPORTER,
ISSUE_STATUS,
CATEGORY,
COMPONENT,
REQUESTED_DATE,
CREATED_DATE,
UPDATED,
RESOLVED,
RESOLUTION,
DAYS_SINCE_LAST_COMMENT,
ISSUE_LINKS,
PROGRESS,
"1ST_RESPONSE_DATE",
LAST_UPDATER,
PARTICIPANTS,
PROCESSED_BY_EIN,
REPORTER_EIN,
LAST_UPDATER_EIN,
QUERY_TEAM_OWNER,
NEXT_ACTION_OWNER,
NEXT_ACT_DIARY_DATE,
ACTION_OWNER_ALLOC_AT,
TOTAL_VOL,
TOTAL_VALID_VOL,
TOTAL_OUTSTANDING_VOL,
TIME_TO_FIRST_RESPONSE,
LOGGED_DATE,
LOGGED_DATE_New,
ISSUE_RECD_DATE,
ISSUE_RECD_DATE_New,
Issue_Rec_Weeks,
ISSUE_CLOSED_DATE,
MONTHname(Date#(ISSUE_CLOSED_DATE,'DD-MMM-YYYY')) as ISSUE_CLOSED_DATE_test,
ISSUE_CLOSER,
ISSUE_LOGGER,
ISSUE_OWNER,
OPEN_DAYS,
AGE_BAND,
OPEN_WKG_DAYS,
AGE_BAND_WKG,
AVG_LOGGED_TIME,
AVG_LOGGED_TIME_BAND,
CLOSED_DAYS,
CLOSED_BAND,
CLOSED_WKG_DAYS,
CLOSED_BAND_WKG,
AVG_CLOSED_TIME,
"MONTH",
CLOSED_WEEK_COMM,
CLOSED_WEEK_ENDING,
FIRST_RESPONSE_DATE,
SLA_TIME_TO_COMP,
SLA_BAND_WKG,
OWNING_MANAGER,
OWNING_MANAGER_SITE
FROM "lib://
(qvd);
==================================================================
//--Master Calendar for Issue Recd Date
Issue_recd_Temp:
Load
min(ISSUE_RECD_DATE_New) as minDate,
max(ISSUE_RECD_DATE_New) as maxDate
Resident Data;
Let varMinDate_Issue_recd = Num(Peek('minDate', 0, 'Issue_recd_Temp'));
Let varMaxDate_Issue_recd = Num(Peek('maxDate', 0, 'Issue_recd_Temp'));
DROP Table Issue_recd_Temp;
TempCalendar_issue_recd_date:
LOAD
$(varMinDate_Issue_recd) + Iterno()-1 As Num_Issue_recd,
Date($(varMaxDate_Issue_recd) + IterNo() - 1) as TempDate_Issue_recd
AutoGenerate 1 While $(varMinDate_Issue_recd) + IterNo() -1 <= $(varMaxDate_Issue_recd);
MasterCalendar_Issue_Recd:
Load
TempDate_Issue_recd AS ISSUE_RECD_DATE_New, // link with EMS table
week(TempDate_Issue_recd) As Issue_recd_Week,
Year(TempDate_Issue_recd) As Issue_recd_Year_NEW,
Month(TempDate_Issue_recd) As Issue_recd_Month,
Day(TempDate_Issue_recd) As Issue_recd_Day,
date(monthstart(TempDate_Issue_recd), 'MMM-YYYY') as Issue_Recd_MonthYear,
Week(weekstart(TempDate_Issue_recd)) & '-' & WeekYear(TempDate_Issue_recd) as Issue_recd_WeekYear,
WeekDay(TempDate_Issue_recd) as Issue_recd_WeekDay
Resident TempCalendar_issue_recd_date
Order By TempDate_Issue_recd ASC;
Drop Table TempCalendar_issue_recd_date;
========================================================
//--Master Calendar for Issue Closed Date ----//
Issue_Closed_Temp:
Load
min(ISSUE_CLOSED_DATE_test) as minDate,
max(ISSUE_CLOSED_DATE_test) as maxDate
Resident Data;
Let varMinDate_Issue_Closed = Num(Peek('minDate', 0, 'Issue_Closed_Temp'));
Let varMaxDate_Issue_Closed = Num(Peek('maxDate', 0, 'Issue_Closed_Temp'));
DROP Table Issue_Closed_Temp;
TempCalendar_issue_Closed_date:
LOAD
$(varMinDate_Issue_Closed) + Iterno()-1 As Num_Issue_Closed,
Date($(varMaxDate_Issue_Closed) + IterNo() - 1) as TempDate_Issue_Closed
AutoGenerate 1 While $(varMinDate_Issue_Closed) + IterNo() -1 <= $(varMaxDate_Issue_Closed);
MasterCalendar_Issue_Closed:
Load
TempDate_Issue_Closed AS ISSUE_CLOSED_DATE, // link with EMS table
week(TempDate_Issue_Closed) As Issue_Closed_Week,
Year(TempDate_Issue_Closed) As Issue_Closed_Year_NEW,
Month(TempDate_Issue_Closed) As Issue_Closed_Month,
Day(TempDate_Issue_Closed) As Issue_Closed_Day,
date(monthstart(TempDate_Issue_Closed), 'MMM-YYYY') as Issue_Closed_MonthYear,
Week(weekstart(TempDate_Issue_Closed)) & '-' & WeekYear(TempDate_Issue_Closed) as Issue_Closed_WeekYear,
WeekDay(TempDate_Issue_Closed) as Issue_Closed_WeekDay
Resident TempCalendar_issue_Closed_date
Order By TempDate_Issue_Closed ASC;
Drop Table TempCalendar_issue_Closed_date;
===============================================
//--Master Calendar for Report Date ----//
Report_Date_Temp:
Load
min(REPORT_DATE) as minDate,
max(REPORT_DATE) as maxDate
Resident Data;
Let varMinDate_Report_Date = Num(Peek('minDate', 0, 'Report_Date_Temp'));
Let varMaxDate_Report_Date = Num(Peek('maxDate', 0, 'Report_Date_Temp'));
DROP Table Report_Date_Temp;
TempCalendar_Report_Date:
LOAD
$(varMinDate_Report_Date) + Iterno()-1 As Num_Report_Date,
Date($(varMaxDate_Report_Date) + IterNo() - 1) as TempDate_Report_Date
AutoGenerate 1 While $(varMinDate_Report_Date) + IterNo() -1 <= $(varMaxDate_Report_Date);
MasterCalendar_Report_Date:
Load
TempDate_Report_Date AS REPORT_DATE, // link with EMS table
week(TempDate_Report_Date) As Report_Week,
Year(TempDate_Report_Date) As Report_Year,
Month(TempDate_Report_Date) As Report_Month,
Day(TempDate_Report_Date) As Report_Day,
date(monthstart(TempDate_Report_Date), 'MMM-YYYY') as Report_MonthYear,
Week(weekstart(TempDate_Report_Date)) & '-' & WeekYear(TempDate_Report_Date) as Report_WeekYear,
WeekDay(TempDate_Report_Date) as Report_WeekDay
Resident TempCalendar_Report_Date
Order By TempDate_Report_Date ASC;
Drop Table TempCalendar_Report_Date;
============================================================================
//-- Create Bridge table (Canonical Date)---//
DateBridge:
Load
ISSUE_NO,
ISSUE_RECD_DATE_New as CanonicalDate,
'Received' as DateType
Resident Data;
Concatenate (DateBridge)
Load
ISSUE_NO,
ISSUE_CLOSED_DATE_test as CanonicalDate,
'Closed' as DateType
Resident Data;
Concatenate (DateBridge)
Load
ISSUE_NO,
REPORT_DATE as CanonicalDate,
'Report' as DateType
Resident Data;
==================================================
//--Master Calendar for Report Date ----//
Temp:
Load
min(CanonicalDate) as minDate,
max(CanonicalDate) as maxDate
Resident DateBridge;
Let varMinDate_Canonical_Date = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate_Canonical_Date = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar_Canonical_Date:
LOAD
$(varMinDate_Canonical_Date) + Iterno()-1 As Num_Canonical_Date,
Date($(varMaxDate_Canonical_Date) + IterNo() - 1) as TempDate_Canonical_Date
AutoGenerate 1 While $(varMinDate_Canonical_Date) + IterNo() -1 <= $(varMaxDate_Canonical_Date);
MasterCalendar_Canonical_Date:
Load
TempDate_Canonical_Date AS CanonicalDate, // link with EMS table
week(TempDate_Canonical_Date) As Canonical_Week,
Year(TempDate_Canonical_Date) As Canonical_Year,
Month(TempDate_Canonical_Date) As Canonical_Month,
Day(TempDate_Canonical_Date) As Canonical_Day,
date(monthstart(TempDate_Canonical_Date), 'MMM-YYYY') as Canonical_MonthYear,
Week(weekstart(TempDate_Canonical_Date)) & '-' & WeekYear(TempDate_Canonical_Date) as Canonical_WeekYear,
WeekDay(TempDate_Canonical_Date) as Canonical_WeekDay
Resident TempCalendar_Canonical_Date
Order By TempDate_Canonical_Date ASC;
Drop Table TempCalendar_Canonical_Date;
===========================================================
Tried to populate the KPI's Zero values:-
=======================================================
And the ideally it should populate below values :-
Right Now, it is giving me zero for all values .
Could you please help me with the same please ?
Thank You
Hi,I would try checking the relations and the data. You can do this adding a table with all the fields of the date bridge and CanonicalDate.
Select Received in DateType and check if there are rows with all data, from IssueNo to CanonicalDate
Hi,
Can anyone please help me with this ?
Thanks
Hi, have you tried to create the table with all fields of the bridge table as dimensions?
Hi @rubenmarin
I took all the dimension. Please see below :-
IT seems the canonical date calendar is not created properly.
Thanks