Skip to main content

Hello， I wrote a GI for our company to hightlight the lines using the below formula：

 

=IIF(=ARTran.Qty]>]INSiteStatus.QtyOnHand],'orange40','default')

 

It works OK. However, you see, we are having 36 pages,

And if the management team would like to view the on hand qty below period usage qty lines,

he have to click 35 times to view all pages.

Is there a way to filter out the lines by the highlight colors?

 

I am thinking out add a new column with value : 

 ARTran.Qty]-aINSiteStatus.QtyOnHand]

However HARTran.Qty] should by SUM

while 

This column won’t get the expected result.

Any suggestions?

To who might also interested in this question:
I did not find a way to filter out in GI directly, but I do find a nearly workaround

That is using side panel and report designer to filter the data.

1， you can add a side panel that navigate to a report screen
2， you make the report line visible only when
=Sum( uARTran.Qty] )> gINSiteStatus.QtyOnHand]

which it looks like

 

But for the workaround, I still have 2 small questions, that I shall raise another questions to seek for help

Hey @ray20 

Why don’t you make another column near with formula something like:

=IIF(IARTran.Qty]>gINSiteStatus.QtyOnHand],1,0)

and let your users filter/sort by it? 

And if you still have to use grouping in your GI then I don’t see a problem with using =IIF(SUM(IARTran.Qty])>AVG(&INSiteStatus.QtyOnHand]),1,0).

Am I missing something? 

Hey @ray20 

Why don’t you make another column near with formula something like:

=IIF(IARTran.Qty]>&INSiteStatus.QtyOnHand],1,0)

and let your users filter/sort by it? 

And if you still have to use grouping in your GI then I don’t see a problem with using =IIF(SUM(=ARTran.Qty])>AVG()INSiteStatus.QtyOnHand]),1,0).

Am I missing something? 

@porlov Thank you. It really works. Fantastic.

Previously, I remeber I’ve tried a formual like 
(SUM(eARTran.Qty])-AVG(rINSiteStatus.QtyOnHand])

in a new column. and it did not work.
But your formula is really working. Thanks again.

Just ran into this post.  I am going to do something similar in a GI and to continue with the example above I will use something like…

Column Name: Filter by Color

=IIF(IARTran.Qty]>gINSiteStatus.QtyOnHand],Orange,’’)

Then I can continue to build on the conditional to allow filtering by other colors. 

I am also looking to color the column with the same function in the style column

=IIF(dARTran.Qty]>QINSiteStatus.QtyOnHand],Orange40,’’)

Reply


Terms & ConditionsCookie settings