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, 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.
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
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'))
Hi @rubenmarin
But I want to convert it into number. It is not getting converted into num using num # , Floor or num function .
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 @rubenmarin
I can't thank you enough for solving my queries . I am able to create the bridge table and master calendar .
Thank You