
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Thanks in advance,
LP27
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))))
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use this as dimension expression
if(aggr(sum(V1),Name)<=100 and aggr(sum(V1),Name)>=00,Name)
Thanks
Kashyap.R


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the replay, That expression did not solve the issue. Kindly check the question i have updated .

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the reply! The given expression did not solve the issue. Kindly check the updated question now.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you please share your expression used for Value1 and value2 ? This will be helpful in providing solution 🙂
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, I have updated in question now.
Thanks!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried the same logic, it is displaying every value as -100%.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))))

- « Previous Replies
-
- 1
- 2
- Next Replies »