topic Re: Display only positive values in Measure in pivot table Qlik Sense. in App Development

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

LP27 — Sat, 16 Nov 2024 19:54:33 GMT

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

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

Kashyap_R — Mon, 14 Oct 2019 04:57:53 GMT

Use this as dimension expression

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

Thanks

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

jyothish8807 — Mon, 14 Oct 2019 05:34:25 GMT

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,

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

LP27 — Mon, 14 Oct 2019 09:14:14 GMT

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

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

LP27 — Mon, 14 Oct 2019 09:15:14 GMT

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

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

jyothish8807 — Mon, 14 Oct 2019 09:19:53 GMT

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

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

LP27 — Mon, 14 Oct 2019 09:23:19 GMT

Yes, I have updated in question now.

Thanks!

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

jyothish8807 — Mon, 14 Oct 2019 10:22:24 GMT

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,

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

LP27 — Mon, 14 Oct 2019 10:40:26 GMT

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

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

LP27 — Mon, 14 Oct 2019 11:04:01 GMT

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

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

jyothish8807 — Mon, 14 Oct 2019 11:04:16 GMT

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

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

LP27 — Mon, 14 Oct 2019 11:16:30 GMT

Awesome! It worked.

Appreciate it!

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

jyothish8807 — Mon, 14 Oct 2019 12:39:11 GMT

You are welcome!

Br,