Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
FirstSortedValue(DATE_TIME, -HIGH)
i am not sure about second part
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
YOU TRY TO BUILD SQL
I WILL COME BACK
TRY TO REPLACE MY sql WITH UR db
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 )