Show data for multiple dates in one single table qliksense

[Aspiring_Developer]

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 

@sunny_talwar @hic @Kushal_Chawda 

[rubenmarin]

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

[Aspiring_Developer]

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.

[rubenmarin · Accepted solution]

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

[Aspiring_Developer]

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

[Aspiring_Developer]

Hello @rubenmarin 

Here is the glimpse of my data model :-

[rubenmarin]

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

[Aspiring_Developer]

Hi,

Can anyone please help me with this ? 

Thanks

[rubenmarin]

Hi, have you tried to create the table with all fields of the bridge table as dimensions?

[Aspiring_Developer]

Hi @rubenmarin 

I took all the dimension. Please see below :-

IT seems the canonical date calendar is not created properly. 

Thanks