Display only positive values in Measure in pivot table Qlik Sense.

[LP27]

Hi Folks,

I have got below scenario where i need to display only positive values and values within 100% only.

Example:

This is my sample pivot table-

Name	Location Rate	Value	Land Rate	Value_2
Limited	$5009	100%	$6556	56%
Showroom	$478	29847%	$457	15%
Kite	$574	98%	$235	-2546%
shop	$787	-459%	$56	-45%

Now i need to display only the values which are positives and below 100% only.

I am sure , i need to write expression in Value(Measures), under show column if. But not sure how to write for the above scenario.

Note: The logic for Value = (1-(Sum({<[C-Desc]={"N/A","0001","N/A(BLANK)","()"}>} DValue)) / (Sum([Location Rate])))

Value2 = (1-(Sum({<[C-Desc]={"N/A","0001","N/A(BLANK)","()"}>} DValue)) / (Sum([Land Rate])))

*C-Desc and Dvalue are others fields.

Thanks in advance,

LP27

[jyothish8807 · Accepted solution]

Hi ,

I believe, you are selecting % option from Number tab, which is converting the number to % in the chart, so try this:

if((1-(Sum({<[C-Desc]={"N/A","0001","N/A(BLANK)","()"}>} DValue)) / (Sum([Location Rate]))) >= 00

and

(1-(Sum({<[C-Desc]={"N/A","0001","N/A(BLANK)","()"}>} DValue)) / (Sum([Location Rate]))) <=01,

(1-(Sum({<[C-Desc]={"N/A","0001","N/A(BLANK)","()"}>} DValue)) / (Sum([Location Rate]))))

[LP27]

Awesome! It worked.

Appreciate it! 

[jyothish8807]

You are welcome!

Br,

KC