Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Suppress null values in dimension

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.

2.png

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.

3.png

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

1.png

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

17 Replies
jyothish8807
Master II
Master II

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

Best Regards,
KC
Anonymous
Not applicable
Author

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.

jyothish8807
Master II
Master II

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

Best Regards,
KC
qlikviewwizard
Master II
Master II

Hi,

Try like this in script.

Use left join or inner join.

Capture.PNG

Anonymous
Not applicable
Author

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)

jyothish8807
Master II
Master II

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

Best Regards,
KC
Anonymous
Not applicable
Author

Thanks KC

jyothish8807
Master II
Master II

You are welcome

Best Regards,
KC