Calculate nearest Min and nearest Max

[Anonymous]

Hi,

ID	Product	Rate	Profit	Loss
1	Sand	5000	1000	501
2	Acrylic Mixture	7000	1500	496
3	Fly Ash Bricks	9000	2000	505
4	Polyurathene	7500	2500	500

I have data like above say around 50,000 rows. What I want is Nearest Loss to 500 (means from 496 to 505 Loss amount) when I give Input as 500 in Inputfield, I should be able to see above related data to Loss amount 500.

I want similar data for 246 to 255 for Loss Amount 250; Similar data for 996 to 1005 for Loss Amount 1000. How to calculate in Straight Box in UI level or in Script Level.

[#2]

Hello,

Please find solution attached.

Some explanation:

1. I have added two fake rows, to test if logic works:

LOAD * INLINE [
    ID, Product, Rate, Profit, Loss
    1, Sand, 5000,    1000,    501
    2, Acrylic Mixture, 7000, 1500, 496
    3, Fly Ash Bricks, 9000, 2000, 505
    4, Polyurathene, 7500, 2500, 500
    5, aaa,1,1,1
    6, bbb,232323,3433445,22452
];

2. Created vInputLoss variable, and added Input box containing it on the screen

3. using input box, value 500 was assigned to the variable

4. I have created a chart with your dimensions and below expression (labelled it "expr"):

IF(Loss > ($(vInputLoss)-5) AND Loss <= ($(vInputLoss) +5),1,0)

5. In Presentation tab, Columns section, i clicked on expr column and marked it to be hidden

6. In Presentation tab of the chart I set "Supress zero values"

Expression resolves to 0 for all rows not fulfilling the criteria, which are then removed from the chart.

BR,

Kuba

[santiago_respane · Accepted solution]

Hi,

create a straight table chart

Add product as dimension

Add Rate and Profit as dimensions

Add expression:

=SUM(IF(Loss >= $(vInput)-$(vVariation) AND Loss <= $(vInput) +$(vVariation),Loss,0))

Where vInput will be your Loss value and vVariation the variation (5 in your case)

So for filtering the values outside Loss +- variation check suppress zero values in the Presentation tab of the table.

Let me know if this helps.

Kind regards,

[antoniotiman]

Hi,

I created variables vRate,vProfit,vLoss and vFlag

In Table Dimension

ID

=Pick(Match(vFlag,1,2,3),
Aggr(If(Sum(Rate) >= vRate*(1-(vNum)) and Sum(Rate) <= vRate*(1+(vNum)),Product),Product),
Aggr(If(Sum(Profit) >= vProfit*(1-(vNum)) and Sum(Profit) <= vProfit*(1+(vNum)),Product),Product),
Aggr(If(Sum(Loss) >= vLoss*(1-(vNum)) and Sum(Loss) <= vRate*(1+(vNum)),Product),Product)
)

Aggregation is ID,Product

Varible vNum is % of Value (+/-)

You have a look at Document Trigger

You don't need to click on Text box

See attachment

Regards,

Antonio

[Anonymous]

Thank you so much Everyone. I shall definitely try and let u know If I have succeeded or not.

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

[Anonymous]

It's working. I'm trying to convert the same into SET analysis. Can you please help me?