topic Re: Remove minus sign and change view in QlikView

Remove minus sign and change view

didierodayo — Fri, 16 Dec 2016 12:21:11 GMT

Hello, I need help with 2 questions

I have the following script providing me with growth rates and months, the script run ok but the resulting table has minus(-) sign as shown below. how do I treat resolve this?

Question 2 .witht the trafficDate_Trend what if I wanted to displa the output as

Metric_Value_Peak_Growth 3MONTH TREND 6MONTHtREND 12MONTHSTREND ,,,,,,,,,,,,,,UP TO 3 YEARS

how can I append the script for the above output?

Thanks

Traffic:
LOAD Null() as Key_Link AutoGenerate(0);
Concatenate(Traffic)
LOAD
Date & '|' & [Node Name] & '|' & [A-End Port FOB] as Key_Link,
[Node Name] & '|' & [Interface Name] & '|' & Metric as Key_DeploymentPlan,
[Node Name] & '|' & [Interface Name] & '|' & Metric as Key_Growth,
Date as TrafficDate,
[Interface Name],
Key_Limits,
[Metric Type],
Metric,
[Node Metric Groups],
[Port Metric Groups],
[Metric Value Peak]
FROM \\c.......\Traffic_WE_*.qvd (qvd);
//Where Exists([Node Port (NDSD)], [Node Name] & '|' & [A-End Port FOB])

TrafficGrowth:
LOAD
DATE( monthstart(today(),iterno()-1)) as TrafficDate_Trend
,monthstart(today(),iterno()-1)*[Metric Slope]+[Metric Intercept] as [Metric Value Peak Growth],
Key_Growth
WHILE iterno() <= 36;

Load
Key_Growth,
LINEST_B([Metric Value Peak], TrafficDate) as [Metric Intercept],
LINEST_M([Metric Value Peak], TrafficDate) as [Metric Slope]
Resident Traffic WHERE TrafficDate >= monthstart(today(),-12)
Group By
Key_Growth;

Re: Remove minus sign and change view

Gysbert_Wassenaar — Fri, 16 Dec 2016 12:48:46 GMT

You can remove the - sign by modifying the number format. Go to the Number tab, select the expression, disable the Expression Default setting and choose Fixed. Then change the number format setting to #,##0;#,##0 The second part after the semicolon is the formatting for negative numbers.

Re: Remove minus sign and change view

didierodayo — Fri, 16 Dec 2016 12:53:53 GMT

Hi Gysbert,

that works but if I am using the field in calculation it will result in errors. is it possible to format it in the script?

Re: Remove minus sign and change view

Gysbert_Wassenaar — Fri, 16 Dec 2016 12:55:53 GMT

You can use the fabs() function to calculate the absolute value. It will basically multiply negative numbers with -1. But you should be really sure that this is the correct way to deal with those numbers.

Re: Remove minus sign and change view

didierodayo — Fri, 16 Dec 2016 13:14:07 GMT

I have tried the fabs() function . the result is wrong. actual is yellow.

Re: Remove minus sign and change view

didierodayo — Fri, 16 Dec 2016 13:34:52 GMT

I added your suggestion to the script with num and it worked. Thanks Gysbert.

Do you think I should raise my second question separately and close this off?