Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Suppress the expressions with null value

Hi Everyone,

I have a question here.

I created a Report Dimension, a Report Metric and a Virtual Dates which is also used as dimension. They look like below:

Dimension Key, Dimension

01, Product Code

02, Product Name

...

Metric Key, Metric

01, Sales Amount

02, Sales Units

...

Virtual Dates Key, Virtual Dates

01, MTD

02, YTD

...

When selecting a single virtual date (MTD), dimension (Product Code) and metric(Sales Amount), the calculation is correct. I got something like

Product Code  MTD

101                 $500

102                 $450

...

But when selecting multiple virtual dates (MTD and YTD), I got the result as below:

                       MTD Sales Amount          YTD Sales Amount

Product Code   MTD          YTD                MTD        YTD

101                 $500            -                    -             $3500

102                 $450            -                    -             $3750

...

I want to suppress those fields with the '-' symble. Does anyone have any idea how I can do that?

Thanks in advance,

Lareina

9 Replies
khadeer_sparks
Valued Contributor

Re: Suppress the expressions with null value

Which chart u r using?

In dimension tab surpress null value is there, take the dimension which u want surpress the null values, click that option.

Hope it helps you

Not applicable

Re: Suppress the expressions with null value

I created a Report table and added Virtual Dates as dimension. I chekced on the toggle 'Suppress When Value Is Null' but it's not working.

neha_shirsath
Valued Contributor

Re: Suppress the expressions with null value

Hii,

In Chart properties > Presentation tab > Check the suppress zero-values.

And see the attachment. Hope it helps you

Not applicable

Re: Suppress the expressions with null value

Hi,

This you can't supress.. YTD and MTD are expressions.. supress null values menas it will supress records when value is null.. but here you are trying to supress expressions.. instead of '-' you can pass '0' value...

Still if you want to hide the null expression then you have to do conditional hide..

Product Code   MTD          YTD                MTD        YTD

101                 $500            -                    -             $3500

102                 $450            -                    -             $3750

Hope it helps..

// Chandra

Not applicable

Re: Suppress the expressions with null value

Thanks for the reply. Unfortunately, supress zero-value doesn't work in my case.

Not applicable

Re: Suppress the expressions with null value

Can you be more specific about 'conditional hide'? I want to suppress those two columns where the calculation conditions are not fulfilled.

This is my expression for MTD

IF ([Virtual Dates]= 'MTD' and [_Report Metric] = 'Sales Amount', Money(Sum({<Year = , Month =, Quarter = >}If (_MTDInvoiceFlag, [Invoice Sales Amount]))))

This is my expression for YTD

IF ([Virtual Dates] = 'YTD' and [_Report Metric] = 'Sales Amount', Money(Sum({<Year = , Month =, Quarter =  >}If (_YTDInvoiceFlag, [Invoice Sales Amount]))))

Not applicable

Re: Suppress the expressions with null value

Hi,

Try like this below..

if(

IF ([Virtual Dates]= 'MTD' and [_Report Metric] = 'Sales Amount', Money(Sum({<Year = , Month =, Quarter = >}If (_MTDInvoiceFlag, [Invoice Sales Amount]))))='0',0,1)

In attached file i given conditional hide in expresion tab for first expression when country='US' then it has to hide.. have look on the file it will give some idea..

in your case i am assuming take if(your YTD expression=0,0,1) same for YTD also..

This conditional hide option is available in Qlikview11 for both Pivot and straight table.. in below versions only for straight table it will be available under presentation tab..

sujeetsingh
Honored Contributor III

Re: Suppress the expressions with null value

You can supress it if you are having it as dimension else just replace - with null or missing

Not applicable

Re: Suppress the expressions with null value

Thanks everyone for the replying. I had solved this. Remove the Virtual Dates from dimension. Cut the condition from the definition and put it into the calculate condition.

In the Expression tab

Conditional:

= SubStringCount(Concat([_Virtual Dates Key], '|'), 01) and SubStringCount(Concat([_Report Metric Key], '|'), 01) and (GetSelectedCount([Virtual Dates]) > 0)

Definition:

Money(Sum({<Year = , Month =, Quarter = >}If (_MTDInvoiceFlag, [Sales Amount])))

And it worked.

Community Browser