topic How to show only negative values for expression? in QlikView

How to show only negative values for expression?

bhaveshp90 — Sat, 16 Nov 2024 21:24:56 GMT

Hello,

I have a table as shown below;

I am trying to show only negative values for YTD, I have tried to use if condition and even sorting is not working in the table.

Where I'm doing wrong here, attached is the file for reference.

thanks

Bhavesh

Re: How to show only negative values for expression?

sunny_talwar — Wed, 13 Feb 2019 17:44:25 GMT

Try this

RangeMin(
((
(
(Sum(Updated_OEC_Budget)-Sum(Forecast_Cost_to_Complete))-(Sum(Prior_Updated_OEC_Budget)-Sum(Prior_Forecast_Cost_to_Complete))
)
*
if(Cost_Code=8,Sum({<Cost_Code={8}>}POC)/100,
if(Cost_Code=10,Sum({<Cost_Code={10}>}POC)/100,
if(Cost_Code=12,Sum({<Cost_Code={12}>}POC)/100,
Sum({<Cost_Code={8,10,12}>}POC)/300)))
)

+

(
	if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Updated_OEC_Budget),
	if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Updated_OEC_Budget),
	if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Updated_OEC_Budget),
	Sum({<Cost_Code={8,10,12}>}Prior_Updated_OEC_Budget))))
	-
	if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Forecast_Cost_to_Complete),
	if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Forecast_Cost_to_Complete),
	if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Forecast_Cost_to_Complete),
	Sum({<Cost_Code={8,10,12}>}Prior_Forecast_Cost_to_Complete))))
)
*
(

	If(Cost_Code=8, Num(
	(Sum({<Cost_Code={8}>}POC)/100
	-
	Alt(Above(TOTAL Sum({<MonthYear,YearMonth,Cost_Code={8}>}POC)/100, Num(Month)),0)),'###.##%'),
	
	If(Cost_Code=10, Num(
	(Sum({<Cost_Code={10}>}POC)/100
	-
	Alt(Above(TOTAL Sum({<MonthYear,YearMonth,Cost_Code={10}>}POC)/100, Num(Month)),0)),'###.##%'),
	
	if(Cost_Code=12,Num(
	(Sum({<Cost_Code={12}>}POC)/100
	-
	Alt(Above(TOTAL Sum({<MonthYear,YearMonth,Cost_Code={12}>}POC)/100, Num(Month)),0)),'###.##%'),
	
	Num(
	(Sum({<Cost_Code={8,10,12}>}POC)/300
	-
	Alt(Above(TOTAL Sum({<MonthYear,YearMonth,Cost_Code={8,10,12}>}POC)/300, Num(Month)),0)),'###.##%'),
	)))

)), 0)

Re: How to show only negative values for expression?

bhaveshp90 — Wed, 13 Feb 2019 18:23:42 GMT

@sunny_talwar when we add other expressions say MTD, we still see the Zero values for these projects which is not correct. Is there any way we can avoid those projects completely?

Re: How to show only negative values for expression?

sunny_talwar — Wed, 13 Feb 2019 18:40:10 GMT

What do you want to see?

Re: How to show only negative values for expression?

bhaveshp90 — Wed, 13 Feb 2019 19:39:58 GMT

@sunny_talwar , I want to see the top 10 MTD values (Projects with Negative values) in Descending order.

I cannot able to sort the MTD expression in the table as shown below

Re: How to show only negative values for expression?

Claudiu_Anghelescu — Thu, 14 Feb 2019 00:31:31 GMT

IF(your_expression < 0 , your_expression)