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

[Kashyap_R]

Use this as dimension expression

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

Thanks

[jyothish8807]

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

[LP27]

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

[LP27]

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

[jyothish8807]

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

[LP27]

Yes, I have updated in question now.

Thanks!

[jyothish8807]

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

[LP27]

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

[LP27]

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