Announcements
cancel
Showing results for
Did you mean:
Creator 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 (5)

• ### Script Logic

1 Solution

Accepted Solutions
Master II

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
Partner - Specialist

Use this as dimension expression

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

Thanks

Thanks and Regards
Kashyap.R
Master II

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
Creator II
Author

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

Creator II
Author

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

Master II

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

Best Regards,
KC
Creator II
Author

Yes, I have updated in question now.

Thanks!

Master II

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
Creator II
Author

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

Creator II
Author

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