Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
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
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,
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
Thank you so much Everyone. I shall definitely try and let u know If I have succeeded or not.
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 .
It's working. I'm trying to convert the same into SET analysis. Can you please help me?