Hi Team,
I am using Percentage difference calculation using table calculation in a pivot table.
I would like to sort the table with Percentage difference i.e. “Delta” in below table-
How can we do that?
Thank you
Amit
@Amit_Raj Hello … I can solve creating new calculated fields …
… and finally ordering by last column.
Let me know if that resolves the issue!
Regards, José Burrull
1 Like
Hi @jose_burrull
Thank you for replying. I attempted it, but unfortunately doesn’t work for me.
I tried as below - but this results in no data, if I remove parseDate I get the error.
sumIf({line_item_unblended_cost}, {line_item_usage_date}=parseDate("2024-07-06 - 2024-07-12"))
even a simple calculation does not work-
sumIf({line_item_unblended_cost}, {line_item_usage_date}=parseDate("2024-07-06"))
I would like to add here that I create the custom date range from the line_item_usage_date column using below custom field.
cal_current_week-
concat(
formatDate(truncDate('DD', {cal_current_week_start}), 'yyyy-MM-dd'),
' - ',
formatDate(truncDate('DD', ${SelectEndDate}), 'yyyy-MM-dd')
)
where cal_current_week_start is
addDateTime(-6, 'DD', ${SelectEndDate})
and SelectEndDate is the user input from a parameter.
and calculating cost like this certainly does not work-
sumIf({line_item_unblended_cost}, {line_item_usage_date}={cal_current_week})
sumIf({line_item_unblended_cost}, {line_item_usage_date}=parseDate({cal_current_week}))
The dates in the column line_item_usage_date are in below format-
2024-07-06T00:00:00.000Z
2024-07-07T00:00:00.000Z
2024-07-08T00:00:00.000Z
Thank you
HI @Amit_Raj ;
¿Did you try to transform the format of date to only month/day/year, even creating some calculated fields and the use of concatenate, format and parse functions to create a new date field in correct format i.e. in date format?. Please read my last post, maybe it will be useful for your issue. Difference between Dates which are in String Format
Hi @jose_burrull
Thank you, this helped! % diff calculated field is working now.
in your example screenshot, How do you keep the date range as column in the pivot-table?
I am able to achieve the Percentage_diff sorting but I lost the date range, when I add date range as column, my table view gets changed.
without date range-
when I add date range-
How you manage to keep the date range at the same time printing the calculations? I see you don’t add date_range in the columns… how do you show date_range without adding it to the column?
Thank you
Amit
Hi @Amit_Raj, please see if these links help:
Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
3 Likes
Thank you @jose_burrull and @Xclipse Yes I am able to sort correctly.
Thank you for being awesome!
1 Like