How to get first day of each month price in a column for each dimension in straight table

[naveen341]

Hi

Can you please help me with getting first day of each month price as new column .

Here is the sample data and expected o/p

Sample Data:

Date	Product	Price
10/1/2018	A	100
10/2/2018	A	200
10/3/2018	A	300
10/4/2018	A	300
10/5/2018	A	300
10/1/2018	B	500
10/2/2018	B	150
10/3/2018	B	300
10/4/2018	B	250
10/5/2018	B	600
10/1/2018	C	20
10/2/2018	C	30
10/3/2018	C	80
10/4/2018	C	110
10/5/2018	C	60

Expected O/P:

Date	Product	Price	First day Price
10/1/2018	A	100	100
10/2/2018	A	200	100
10/3/2018	A	300	100
10/4/2018	A	300	100
10/5/2018	A	300	100
10/1/2018	B	500	500
10/2/2018	B	150	500
10/3/2018	B	300	500
10/4/2018	B	250	500
10/5/2018	B	600	500
10/1/2018	C	20	20
10/2/2018	C	30	20
10/3/2018	C	80	20
10/4/2018	C	110	20
10/5/2018	C	60	20

Appreciate your help

[sunny_talwar]

Try this as an expression

FirstSortedValue(TOTAL <Product> Price, Date)

[naveen341]

Hi Sunny

its not working

I tried the expression

only( Aggr( only({$<Date=,Date={'$(=Monthstart(max(Date)))'}>} Price),Date,Product))\

But it gave me an output

Date	Product	Price	First day Price
10/1/2018	A	100	100
10/1/2018	B	500	500
10/1/2018	C	20	20
10/2/2018	A	200
10/2/2018	B	150
10/2/2018	C	30
10/3/2018	A	300
10/3/2018	B	300
10/3/2018	C	80
10/4/2018	A	300
10/4/2018	B	250
10/4/2018	C	110
10/5/2018	A	300
10/5/2018	B	600
10/5/2018	C	60

can you please help me with this

Expected output

Date	Product	Price	First day Price
10/1/2018	A	100	100
10/1/2018	B	500	500
10/1/2018	C	20	20
10/2/2018	A	200	100
10/2/2018	B	150	500
10/2/2018	C	30	20
10/3/2018	A	300	100
10/3/2018	B	300	500
10/3/2018	C	80	20
10/4/2018	A	300	100
10/4/2018	B	250	500
10/4/2018	C	110	20
10/5/2018	A	300	100
10/5/2018	B	600	500
10/5/2018	C	60	20

[sunny_talwar]

Worked for me

Can you check your expression one more time. Also, do you have an expression for Price? or Price is a field?

[naveen341]

Yes i do have an expression for price field.

=(avg({<Year=, Quarter=, Month=, Week=, Date={'>=$(=Monthstart(max(Date)))<=$(=max(Date))'}>} Price))

[naveen341]

KeyDate	Index Ticker	=(avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=10/01/2018<=10/05/2018'}>} PX_Last ))	only( Aggr( only({$<KeyDate=,KeyDate={'10/01/2018'}>} PX_Last ),KeyDate,[Index Ticker]))//=avg( Aggr(avg({$<KeyDate={'$(=Monthstart(max(KeyDate)))'}>} PX_Last ),[Index Ticker],KeyDate))
		644.90873333
10/01/2018	G8QI	375.05500000	375.0550
10/01/2018	JPEIGLBL	778.67000000	778.6700
10/01/2018	JPEIPLUS	794.76000000	794.7600
10/02/2018	G8QI	376.57500000
10/02/2018	JPEIGLBL	778.09000000
10/02/2018	JPEIPLUS	793.74000000
10/03/2018	G8QI	370.21100000
10/03/2018	JPEIGLBL	777.15000000
10/03/2018	JPEIPLUS	792.12000000
10/04/2018	G8QI	367.26100000
10/04/2018	JPEIGLBL	771.11000000
10/04/2018	JPEIPLUS	784.22000000
10/05/2018	G8QI	363.73900000
10/05/2018	JPEIGLBL	769.08000000
10/05/2018	JPEIPLUS	781.85000000

This is my orginal fields and data.

first expression:=(avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=$(=Monthstart(max(KeyDate)))<=$(=max(KeyDate))'}>} PX_Last ))

second expression: only( Aggr( only({$<KeyDate=,KeyDate={'$(=Monthstart(max(KeyDate)))'}>} PX_Last ),KeyDate,[Index Ticker]))

[sunny_talwar]

Try this

FirstSortedValue({<Year, Quarter, Month, Week, Date = {'>=$(=Monthstart(Max(Date)))<=$(=Max(Date))'}>} TOTAL <Product> Aggr(Avg({<Year, Quarter, Month, Week, Date = {'>=$(=Monthstart(Max(Date)))<=$(=Max(Date))'}>} Price), KeyDate, [Index Ticker]), Aggr(Only({<Year, Quarter, Month, Week, Date = {'>=$(=Monthstart(Max(Date)))<=$(=Max(Date))'}>} KeyDate), KeyDate, [Index Ticker]))

[naveen341]

Awesome Sunny,

thanks Much this worked.

[sunny_talwar]

Great, please close the thread by marking correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[naveen341]

HI Sunny

Can you please help me with month year field .

I mean if my dimension is like jan-2018

first expression is :

=avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=$(=YearStart(max(KeyDate)))<=$(=max(KeyDate))'}>} PX_Last ).

How do i get the month avgerage in all months.

I tried this expression but its not giving the excat o/p

(FirstSortedValue({<Year, Quarter, Month, Week, Date = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} TOTAL <[Index Ticker]> Aggr(avg({<Year, Quarter, Month, Week, Date = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} PX_Last),

KeyDate, [Index Ticker]), Aggr(avg({<Year, Quarter, Month, Week, Date = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} KeyDate), KeyDate, [Index Ticker])))