# Qlik Sense App Development

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

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

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.

LP27

Labels (6)

• ### sunny_talwar

1 Solution

Accepted Solutions
Highlighted
Honored Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

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

Best Regards,
KC
12 Replies
Highlighted Partner

## Re: Display only positive values in Measure in pivot table Qlik Sense.

Use this as dimension expression

if(aggr(sum(V1),Name)<=100 and aggr(sum(V1),Name)>=00,Name)

Thanks

Highlighted
Honored Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

Hi,

May be like this:

Since you are showing %, assuming your expression would be something like below. Create a new expression:

if((sum(Sales)/Sum(Total Sales))>=0 and (sum(Sales)/Sum(Total Sales)<=1,(sum(Sales)/Sum(Total Sales),null())

Br,

KC

Best Regards,
KC
Highlighted
Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

Thanks for the replay, That expression did not solve the issue. Kindly check the question i have updated .

Highlighted
Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

Thanks for the reply! The given expression did not solve the issue. Kindly check the updated question now.

Highlighted
Honored Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

Can you please share your expression used for Value1 and value2  ? This will be helpful in providing solution 🙂

Best Regards,
KC
Highlighted
Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

Yes, I have updated in question now.

Thanks!

Highlighted
Honored Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

Hi,

I believe the same logic should work in this case as well:

Value1:

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

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

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

Same goes for value 2 expression.

Br,

KC

Best Regards,
KC
Highlighted
Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

I tried the same logic, it is displaying every value as -100%.

Highlighted
Contributor II

## Re: Display only positive values in Measure in pivot table Qlik Sense.

I tried the below expression, this works fine by removing negative values but it displays values greater than 100% too(which should not)  -

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]))) <=100,

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