Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have created pivot table for rolling seven days. Here am facing one issue in suppress null values.
Requirement:
I have to show the number of unapproved deviations count in last seven days by Company wise.
In my data source i don't have data for all days, for example if any deviation got closed on that day data will be entered into source. So business expecting 7 days data if data is not available include master calendar and add those days also so i have created like this by using master calendar.
Here i need to exclude the highlighted '- ' Company values.
why the '-' has appeared in source itself I don't have data so the remaining days (0726,07/27,07/28,07/29,07/30) has not mapped in source.
FYI
In straight table it's not mapped
I have tried in dimension & presentation tab to exclude the null values and also i tried to exclude the '-' values by using calculated dimension (not equal to and length) but it's not working as we expected.
Finally am expecting output like second screenshot to exclude the suppress null value(highlighted area only).
Regards,
Yuvaraj G
One work around would be to put a text box to hide that column, if you are sure that you will will have only one company.
Else try to map Comapny "AAA" to all the dates. or one way will be to write multiple expression for each dates.
Exp 1 for "17th
Exp2 for 18th
Exp 3 for 19th...etc
Br,
KC
No. Here we are showing rolling seven days and also company will change dynamically. So we we cant able to put text object to hide the row.
Hi,
Then write 7 expression.
Dimension: Only company
Exp1: Sum({<Date={'date(Max(Date))'}>}Sales)
Exp2: Sum({<Date={'date(Max(Date)-1)'}>}Sales)
Exp3:Sum({<Date={'date(Max(Date)-2)'}>}Sales) ...............and so on.
This will full fill your requirment.
Br,
KC
Hi,
Try like this in script.
Use left join or inner join.
Hi,
I have tried like you mentioned above but its not working properly. Please check what am missing here
sum({<Date={'Date(Max(CorrectionCompletedTime)-1)'}>}NumberUnapprovedDeviations)
Hi,
Try this:
Sum(DISTINCT{<Year=, Month=,CorrectionCompletedTime={'$(=Date(Date#(Max(CorrectionCompletedTime),'MM/DD/YYYY'),'MM/DD/YYYY'))'}>} NumberUnapprovedDeviations )
Also refer the attachment.
Br,
KC
Thanks KC
You are welcome