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 

[Aspiring_Developer]

Hi @rubenmarin 

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

Thanks

[rubenmarin]

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.

[Aspiring_Developer]

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

[rubenmarin · Accepted solution]

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

[Aspiring_Developer]

Hi @rubenmarin 

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

[rubenmarin · Accepted solution]

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

[Aspiring_Developer]

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