Percentage based on dates

[V_Andrade]

Hi guys,  I hope it's all good.

I've been stuck in a simple thing, but it's complicated for me, because I'm very new at Qlikview and set analysis.

I know how to get the lowest and highest value and make this as increase or decrease percentage using the "Max" and "Min" functions.

But what I'm trying to do is to get the highest value in the HIGH column first and then for the first expression the lowest value in the LOW column, based on dates before the highest date. 
For the second expression the lowest value after highest date.

For example:

The highest value :  97,580  (23/01/2019 08:00)

The lowest value BEFORE (23/01/2019 08:00) :  43,800  (21/01/2019 04:00)
The lowest value AFTER (23/01/2019 08:00):  43,500  (23/01/2019 21:00)

Expression 1st:  an increase of 122.79%

Expression 2st:  a decrease of 55.42%

PRODUCT	LOW	HIGH	DATE_TIME
A	44.150	44.770	21/01/2019 00:00
A	44.090	44.390	21/01/2019 01:00
A	44.010	44.440	21/01/2019 02:00
A	43.970	44.470	21/01/2019 03:00
A	43.800	44.360	21/01/2019 04:00
A	43.860	44.900	21/01/2019 05:00
A	44.130	44.490	21/01/2019 06:00
A	44.130	44.710	21/01/2019 07:00
A	43.860	44.490	21/01/2019 08:00
A	43.850	44.260	21/01/2019 09:00
A	44.230	45.310	21/01/2019 10:00
A	44.720	45.700	21/01/2019 11:00
A	45.200	45.720	21/01/2019 12:00
A	44.750	45.380	21/01/2019 13:00
A	44.670	46.400	21/01/2019 14:00
A	45.470	50.500	21/01/2019 15:00
A	47.690	52.900	21/01/2019 16:00
A	49.130	56.320	21/01/2019 17:00
A	53.550	59.530	21/01/2019 18:00
A	55.550	58.060	21/01/2019 19:00
A	53.970	57.000	21/01/2019 20:00
A	53.560	55.400	21/01/2019 21:00
A	53.950	55.750	21/01/2019 22:00
A	53.360	55.680	21/01/2019 23:00
A	53.650	55.280	22/01/2019 00:00
A	54.140	58.140	22/01/2019 01:00
A	53.930	56.190	22/01/2019 02:00
A	51.600	54.670	22/01/2019 03:00
A	52.050	52.790	22/01/2019 04:00
A	52.120	53.020	22/01/2019 05:00
A	52.200	53.500	22/01/2019 06:00
A	51.000	52.720	22/01/2019 07:00
A	50.220	56.340	22/01/2019 08:00
A	52.070	55.310	22/01/2019 09:00
A	52.870	55.000	22/01/2019 10:00
A	52.090	54.050	22/01/2019 11:00
A	53.050	55.100	22/01/2019 12:00
A	55.030	58.500	22/01/2019 13:00
A	55.520	62.500	22/01/2019 14:00
A	60.070	64.370	22/01/2019 15:00
A	59.320	62.230	22/01/2019 16:00
A	58.600	61.190	22/01/2019 17:00
A	58.580	59.970	22/01/2019 18:00
A	57.930	61.580	22/01/2019 19:00
A	59.800	63.710	22/01/2019 20:00
A	60.440	63.660	22/01/2019 21:00
A	61.600	64.500	22/01/2019 22:00
A	63.200	64.500	22/01/2019 23:00
A	63.260	69.690	23/01/2019 00:00
A	67.850	74.990	23/01/2019 01:00
A	72.000	78.800	23/01/2019 02:00
A	71.510	76.470	23/01/2019 03:00
A	71.670	73.760	23/01/2019 04:00
A	73.230	79.980	23/01/2019 05:00
A	75.130	85.780	23/01/2019 06:00
A	80.680	89.200	23/01/2019 07:00
A	88.500	97.580	23/01/2019 08:00
A	79.370	93.440	23/01/2019 09:00
A	76.560	81.600	23/01/2019 10:00
A	73.690	80.390	23/01/2019 11:00
A	74.830	77.600	23/01/2019 12:00
A	75.120	78.990	23/01/2019 13:00
A	72.800	75.880	23/01/2019 14:00
A	73.240	75.270	23/01/2019 15:00
A	72.110	74.400	23/01/2019 16:00
A	72.340	74.800	23/01/2019 17:00
A	73.800	78.740	23/01/2019 18:00
A	73.320	77.650	23/01/2019 19:00
A	51.060	53.140	23/01/2019 20:00
A	43.500	52.980	23/01/2019 21:00
A	51.520	52.570	23/01/2019 22:00
A	51.550	52.740	23/01/2019 23:00

Any help would be greatly appreciated,

Thanks in advance,

Regards,

V. Andrade

[Channa]

FirstSortedValue(DATE_TIME, -HIGH)

i am not sure about second part

[Channa]

BRO IF DONT GET luck with qlik

try build query like below

SELECT
MIN_MAX.COUNTRY_OF_ORIGIN AS MIN_CON,MIN_MAX.MIN_DATE ,MIN_MAX.MIN_CUSTOMS_VALUE_QAR,
MIN_MAX.RELEASE_DATE,MIN_MAX.CUSTOMS_VALUE_QAR FROM
(
SELECT I.COUNTRY_OF_ORIGIN,F.RELEASE_DATE AS MIN_DATE,I.CUSTOMS_VALUE_QAR AS MIN_CUSTOMS_VALUE_QAR,
MAX_TABLE.RELEASE_DATE,MAX_TABLE.CUSTOMS_VALUE_QAR
from GDC_PRO.FACT_DECLARATION F
JOIN GDC_PRO.TCUST_DEC_DECLARATION D on (F.DECLARATION_ID = D.DECLARATION_ID AND D.DOCUMENT_STATUS='FIN' AND D.DECLARATION_TYPE='IMP')
JOIN "GDC_PRO"."TCUST_DEC_ITEM" I ON (D.DECLARATION_ID=I.DECLARATION_ID)
AND I.CUSTOMS_VALUE_QAR>100
JOIN

(SELECT I.COUNTRY_OF_ORIGIN,F.RELEASE_DATE,I.CUSTOMS_VALUE_QAR
from GDC_PRO.FACT_DECLARATION F
JOIN GDC_PRO.TCUST_DEC_DECLARATION D on (F.DECLARATION_ID = D.DECLARATION_ID AND D.DOCUMENT_STATUS='FIN' AND D.DECLARATION_TYPE='IMP')
JOIN "GDC_PRO"."TCUST_DEC_ITEM" I ON (D.DECLARATION_ID=I.DECLARATION_ID)
WHERE I.CUSTOMS_VALUE_QAR =(SELECT mAX(CUSTOMS_VALUE_QAR) FROM "GDC_PRO"."TCUST_DEC_ITEM"))MAX_TABLE

ON I.COUNTRY_OF_ORIGIN=MAX_TABLE.COUNTRY_OF_ORIGIN
AND F.RELEASE_DATE<MAX_TABLE.RELEASE_DATE) MIN_MAX
WHERE MIN_MAX.MIN_CUSTOMS_VALUE_QAR =(
SELECT MIN(I.CUSTOMS_VALUE_QAR )
from GDC_PRO.FACT_DECLARATION F
JOIN GDC_PRO.TCUST_DEC_DECLARATION D on (F.DECLARATION_ID = D.DECLARATION_ID AND D.DOCUMENT_STATUS='FIN' AND D.DECLARATION_TYPE='IMP')
JOIN "GDC_PRO"."TCUST_DEC_ITEM" I ON (D.DECLARATION_ID=I.DECLARATION_ID)
AND I.CUSTOMS_VALUE_QAR>100
JOIN

(SELECT I.COUNTRY_OF_ORIGIN,F.RELEASE_DATE,I.CUSTOMS_VALUE_QAR
from GDC_PRO.FACT_DECLARATION F
JOIN GDC_PRO.TCUST_DEC_DECLARATION D on (F.DECLARATION_ID = D.DECLARATION_ID AND D.DOCUMENT_STATUS='FIN' AND D.DECLARATION_TYPE='IMP')
JOIN "GDC_PRO"."TCUST_DEC_ITEM" I ON (D.DECLARATION_ID=I.DECLARATION_ID)
WHERE I.CUSTOMS_VALUE_QAR =(SELECT mAX(CUSTOMS_VALUE_QAR) FROM "GDC_PRO"."TCUST_DEC_ITEM"))MAX_TABLE

ON I.COUNTRY_OF_ORIGIN=MAX_TABLE.COUNTRY_OF_ORIGIN
AND F.RELEASE_DATE<MAX_TABLE.RELEASE_DATE)

this sql extract MIN value before max value date

try if positive i will include min after max value date

[V_Andrade]

Hi bro, thanks for the answer,

I tried this:
if(firstsortedvalue (DISTINCT DATE_TIME, -HIGH) > firstsortedvalue (DISTINCT DATE_TIME, LOW), ((Max(HIGH)-Min(LOW)) / Min(LOW)),-( (Max(HIGH)-Min(LOW)) / Max(HIGH) ))

But this isn't what I'm looking for, because it gets the LOW value from any time/date. And I need one expression for the lowest value BEFORE the highest value date and another one for the lowest value AFTER the highest value date as my example above.

[Channa]

YOU TRY TO BUILD SQL

I WILL COME BACK

[Channa]

TRY TO REPLACE MY sql WITH UR db

[Channa]

IT IS SIMPLE REPLACE WITH YOUR TABLE IT SHOULD BRING MAX AND MIN VALUE BEFORE MAX VALUE DATE

SELECT MIN_MAX.DECLARATION_TYPE,MIN_DATE,MIN_AMOUNT_PAID,MAX_VAL_DATE,MAX_VAL FROM

(SELECT F.DECLARATION_TYPE,F.RELEASE_DATE AS MIN_DATE,F.AMOUNT_PAID AS MIN_AMOUNT_PAID,
MAX_TABLE.RELEASE_DATE AS MAX_VAL_DATE,MAX_TABLE.AMOUNT_PAID AS MAX_VAL
from GDC_PRO.FACT_DECLARATION F

JOIN

(SELECT F.DECLARATION_TYPE,F.RELEASE_DATE,F.AMOUNT_PAID
from GDC_PRO.FACT_DECLARATION F

WHERE F.AMOUNT_PAID =(SELECT mAX(AMOUNT_PAID) FROM "GDC_PRO"."FACT_DECLARATION"))MAX_TABLE
ON F.DECLARATION_TYPE=MAX_TABLE.DECLARATION_TYPE
AND F.RELEASE_DATE<MAX_TABLE.RELEASE_DATE) MIN_MAX
WHERE MIN_MAX.MIN_AMOUNT_PAID=

(SELECT MIN(F.AMOUNT_PAID)
from GDC_PRO.FACT_DECLARATION F

JOIN

(SELECT F.DECLARATION_TYPE,F.RELEASE_DATE,F.AMOUNT_PAID
from GDC_PRO.FACT_DECLARATION F

WHERE F.AMOUNT_PAID =(SELECT mAX(AMOUNT_PAID) FROM "GDC_PRO"."FACT_DECLARATION"))MAX_TABLE
ON F.DECLARATION_TYPE=MAX_TABLE.DECLARATION_TYPE
AND F.RELEASE_DATE<MAX_TABLE.RELEASE_DATE )