Suppress the expressions with null value

[#1]

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

[khadeer]

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

[#3]

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]

Hii,

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

And see the attachment. Hope it helps you

[#5]

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

[#6]

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

[#7]

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]))))

[#8]

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]

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

[#10]

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.