Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Hii,
In Chart properties > Presentation tab > Check the suppress zero-values.
And see the attachment. Hope it helps you
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
Thanks for the reply. Unfortunately, supress zero-value doesn't work in my case.
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]))))
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..
You can supress it if you are having it as dimension else just replace - with null or missing
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.