topic Re: Suppress null values in dimension in QlikView

Suppress null values in dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Suppress null values in dimension

qlikviewwizard — Fri, 03 Aug 2018 02:40:48 GMT

Hi,

can you share the sample application?

Re: Suppress null values in dimension

Anonymous — Fri, 03 Aug 2018 03:26:25 GMT

Am not able to attach the qvw file.

Re: Suppress null values in dimension

qlikviewwizard — Fri, 03 Aug 2018 04:00:43 GMT

Try like this.

Re: Suppress null values in dimension

Anonymous — Fri, 03 Aug 2018 04:07:30 GMT

Hi Arjun,

Please find the attachment.

Re: Suppress null values in dimension

jyothish8807 — Fri, 03 Aug 2018 04:29:02 GMT

Hi Yuvaraj,

You can suppress the value by simply checking the "Suppress Null Value" option.

Re: Suppress null values in dimension

Anonymous — Fri, 03 Aug 2018 04:34:55 GMT

I need in between days from 07/17 to 07/23 date also, please refer second screenshot highlighted in red color

Re: Suppress null values in dimension

jyothish8807 — Fri, 03 Aug 2018 04:50:40 GMT

Hi Yuvaraj,

As per your data model the other dates are mapped to a blank field, so it wont be possible to suppress this part unless you fix your mapping.

Re: Suppress null values in dimension

BalaBhaskar_Qlik — Fri, 03 Aug 2018 04:55:09 GMT

Chain	Category
Chain 1	19.8%

Chain 1.1 10.3%

Chain 1.2 11.0%

Chain 2	0.4%
Chain 3	14.6%
Chain 4	3.3%
Chain 5	2.8%
Chain 6	0.4%
-	100.0%

Your data might be, same like above, then try your expression like this:

sum(Spend)/sum({<Chain=>} total <Category> Spend)

Try like this:

If(Sum({$<CTI_DESC1={"OPEN DISC (100%)"}>}QTY) = 0, Null(), Sum({$<CTI_DESC1={"OPEN DISC (100%)"}>}QTY))

Re: Suppress null values in dimension

Anonymous — Fri, 03 Aug 2018 04:57:57 GMT

is that possible to hide that particular column like below screenshot

Re: Suppress null values in dimension

jyothish8807 — Fri, 03 Aug 2018 05:17:17 GMT

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,

Re: Suppress null values in dimension

Anonymous — Fri, 03 Aug 2018 05:25:18 GMT

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.

Re: Suppress null values in dimension

jyothish8807 — Fri, 03 Aug 2018 05:46:07 GMT

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,

Re: Suppress null values in dimension

qlikviewwizard — Fri, 03 Aug 2018 05:52:38 GMT

Hi,

Try like this in script.

Use left join or inner join.

Re: Suppress null values in dimension

Anonymous — Fri, 03 Aug 2018 06:21:32 GMT

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)

Re: Suppress null values in dimension

jyothish8807 — Fri, 03 Aug 2018 07:01:11 GMT

Hi,

Try this:

Sum(DISTINCT{<Year=, Month=,CorrectionCompletedTime={'$(=Date(Date#(Max(CorrectionCompletedTime),'MM/DD/YYYY'),'MM/DD/YYYY'))'}>} NumberUnapprovedDeviations )

Also refer the attachment.

Br,

Re: Suppress null values in dimension

Anonymous — Fri, 03 Aug 2018 10:02:03 GMT

Thanks KC

Re: Suppress null values in dimension

jyothish8807 — Fri, 03 Aug 2018 10:24:49 GMT

You are welcome