topic Re: Calculate nearest Min and nearest Max in QlikView

Calculate nearest Min and nearest Max

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculate nearest Min and nearest Max

Wed, 10 Aug 2016 18:12:35 GMT

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

Re: Calculate nearest Min and nearest Max

santiago_respane — Wed, 10 Aug 2016 18:25:09 GMT

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,

Re: Calculate nearest Min and nearest Max

antoniotiman — Wed, 10 Aug 2016 19:57:00 GMT

Hi,

I created variables vRate,vProfit,vLoss and vFlag

In Table Dimension

=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

Re: Calculate nearest Min and nearest Max

Anonymous — Thu, 11 Aug 2016 06:50:11 GMT

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

Re: Calculate nearest Min and nearest Max

oknotsen — Thu, 11 Aug 2016 08:37:46 GMT

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 .

Re: Calculate nearest Min and nearest Max

Anonymous — Thu, 11 Aug 2016 09:20:24 GMT

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