topic Re: Show data for multiple dates in one single table qliksense in App Development

Show data for multiple dates in one single table qliksense

Aspiring_Developer — Fri, 11 Nov 2022 13:04:55 GMT

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

Re: Show data for multiple dates in one single table qliksense

rubenmarin — Fri, 11 Nov 2022 13:57:59 GMT

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

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Fri, 11 Nov 2022 14:05:16 GMT

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.

Re: Show data for multiple dates in one single table qliksense

rubenmarin — Sat, 12 Nov 2022 08:40:12 GMT

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

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Tue, 15 Nov 2022 15:03:17 GMT

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

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Tue, 15 Nov 2022 15:07:22 GMT

Hello @rubenmarin

Here is the glimpse of my data model :-

Re: Show data for multiple dates in one single table qliksense

rubenmarin — Wed, 16 Nov 2022 10:07:29 GMT

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

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Fri, 18 Nov 2022 19:16:56 GMT

Hi,

Can anyone please help me with this ?

Thanks

Re: Show data for multiple dates in one single table qliksense

rubenmarin — Mon, 21 Nov 2022 07:30:42 GMT

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

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Thu, 24 Nov 2022 06:28:05 GMT

Hi @rubenmarin

I took all the dimension. Please see below :-

IT seems the canonical date calendar is not created properly.

Thanks

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Thu, 24 Nov 2022 07:05:46 GMT

Hi @rubenmarin

Below are my original date format present inside the data table :-

Thanks

Re: Show data for multiple dates in one single table qliksense

rubenmarin — Thu, 24 Nov 2022 07:14:32 GMT

Hi, if you select some values on the Canonica_month fields you could see with values are related with months and what's the difference.

From your calendar I see something wrong, this kinfd of lines:

Date($(varMaxDate_Issue_recd) + IterNo() - 1) as TempDate_Issue_recd -> It should start using Min, not Max

Another cause could be because dates are timestamps and have the hour part, just not showing because are shown as dates, in that case you can apply floor to remove the time part:

Let varMinDate_Canonical_Date = Num(Floor(Peek('minDate', 0, 'Temp')));

Let varMaxDate_Canonical_Date = Num(Floor(Peek('maxDate', 0, 'Temp')));

Or some fields have different date formats, I usually use the link fields of date with interger rounded numbers, calling his fields IdDate, and another field Date with the date format to show on front-end.

There could be different reasons but looking for the different values of the tables (the ones with Canonical_month and the ones that has a Canonical_month) can give you hints on what's happening.

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Thu, 24 Nov 2022 07:40:03 GMT

Hi @rubenmarin

Thanks for your observation. I checked and corrected the date format for Issue Recd Date by making few changes in Master calendar and data table script like below :-

//----Data table --//

Floor(num(ISSUE_RECD_DATE)) as Num_Issue_Recd_Date, // converting data into num

//----------------Master Calendar for issue recd date -----//

Now , i am trying to fix the date format for issue closed date . it seems this date is in text format :-

Floor(num(ISSUE_CLOSED_DATE)) as Num_Issue_Closed_Date, // converting data into num

I think the problem here is in the date format. Because of which the canonical dates are not populating.

Thanks in advance

Re: Show data for multiple dates in one single table qliksense

rubenmarin — Thu, 24 Nov 2022 07:46:07 GMT

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'))

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Thu, 24 Nov 2022 07:57:49 GMT

Hi @rubenmarin

But I want to convert it into number. It is not getting converted into num using num # , Floor or num function .

Re: Show data for multiple dates in one single table qliksense

rubenmarin — Thu, 24 Nov 2022 07:59:55 GMT

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'))

Re: Show data for multiple dates in one single table qliksense

Aspiring_Developer — Thu, 24 Nov 2022 08:38:37 GMT

Hi @rubenmarin

I can't thank you enough for solving my queries . I am able to create the bridge table and master calendar .

Thank You