topic price evolution on 2 different dates in QlikView

price evolution on 2 different dates

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

Hi there,

i have one big data table containing:

Date	Product	Price
23-Apr	1	100
24-Apr	1	100
25-Apr	1	120
26-Apr	1	120
27-Apr	1	120
28-Apr	1	120
23-Apr	2	430
24-Apr	2	430
25-Apr	2	430
26-Apr	2	430
27-Apr	2	430
28-Apr	2	430
23-Apr	3	50
24-Apr	3	40
25-Apr	3	40
26-Apr	3	50
27-Apr	3	50
28-Apr	3	40
23-Apr	4	250
24-Apr	4	250
25-Apr	4	250
26-Apr	4	250
27-Apr	4	250
28-Apr	4	280

when selecting 2 dates (28 apr & 23 apr)

i want to obtain the following chart table, comparing the evolution of price from min(date) = 23 apr until max(date) = 28 apr:

	Price Increase	Price constant	Price decrease
No Products	2	1	1

how can i do this?

Thx,

Andrei

Re: price evolution on 2 different dates

MK_QSL — Sat, 28 Apr 2018 10:30:00 GMT

Create a Straight Table

Use Calculated Dimension

='Number Of Products'

Expressions

Price Increase

SUM(Aggr(IF(Only({<Date = {"$(=Date(Min(Date)))"}>}Price) - Only({<Date = {"$(=Date(Max(Date)))"}>}Price) > 0, 1),Product))

Price Constant

SUM(Aggr(IF(Only({<Date = {"$(=Date(Min(Date)))"}>}Price) - Only({<Date = {"$(=Date(Max(Date)))"}>}Price) = 0, 1),Product))

Price Decrease

SUM(Aggr(IF(Only({<Date = {"$(=Date(Min(Date)))"}>}Price) - Only({<Date = {"$(=Date(Max(Date)))"}>}Price) < 0, 1),Product))

HTH

Regards,

Re: price evolution on 2 different dates

shiveshsingh — Sat, 28 Apr 2018 12:14:14 GMT

Try this.

Re: price evolution on 2 different dates

shiveshsingh — Sat, 28 Apr 2018 12:16:16 GMT

or may be this

=sum(aggr(if (sum({<Date= {"$(=date(max(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price)> sum({<Date= {"$(=date(min(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price),1,0),Product))

=sum(aggr(if (sum({<Date= {"$(=date(max(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price)< sum({<Date= {"$(=date(min(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price),1,0),Product))

=sum(aggr(if (sum({<Date= {"$(=date(max(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price)= sum({<Date= {"$(=date(min(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price),1,0),Product))

Re: price evolution on 2 different dates

MarcoWedel — Sat, 28 Apr 2018 22:48:50 GMT

Hi,

another solution might be:

=Aggr('Price '&Pick(Sign(FirstSortedValue(Price,-Date)-FirstSortedValue(Price,Date))+2,'decrease','constant','increase'),Product)

hope this helps

regards

Marco