Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LP27
Creator II
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-

NameLocation RateValueLand RateValue_2
Limited$5009100%$655656%
Showroom$47829847%$45715%
Kite$57498%$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

Labels (5)
1 Solution

Accepted Solutions
jyothish8807
Master II
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

View solution in original post

12 Replies
Kashyap_R
Partner - Specialist
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
jyothish8807
Master II
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
LP27
Creator II
Creator II
Author

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

LP27
Creator II
Creator II
Author

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

jyothish8807
Master II
Master II

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

Best Regards,
KC
LP27
Creator II
Creator II
Author

Yes, I have updated in question now.

Thanks!

jyothish8807
Master II
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
LP27
Creator II
Creator II
Author

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

LP27
Creator II
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]))))