Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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-

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

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

View solution in original post

12 Replies
Highlighted
Partner
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]))))