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]

I am not sure I follow

[naveen341]

when i use month year for other chart.

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

getting jan 2018 value for complete year

second expression:

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

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

second expression is not giving the exact o/p,

Can you please help me

MonthYear	Index Ticker	monthly	(FirstSortedValue({<Year, Quarter, Month, Week, KeyDate = {'>=01/01/2018<=07/31/2018'}>} TOTAL <[Index Ticker]> Aggr(avg({<Year, Quarter, Month, Week, KeyDate = {'>=01/01/2018<=07/31/2018'}>} PX_Last), KeyDate, [Index Ticker]), Aggr(avg({<Year, Quarter, Month, Week, KeyDate = {'>=01/01/2018<=07/31/2018'}>} KeyDate), KeyDate, [Index Ticker]))) //=avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=$(=YearStart(max(KeyDate)))<=$(=max(KeyDate))'}>} PX_Last )
		658.86792105
Jan 2018	G8QI	390.11295652	396.07400000
Jan 2018	JPEIGLBL	807.87869565	807.95000000
Jan 2018	JPEIPLUS	834.06739130	836.13000000
Feb 2018	G8QI	374.65700000	396.07400000
Feb 2018	JPEIGLBL	791.63300000	807.95000000
Feb 2018	JPEIPLUS	815.27950000	836.13000000
Mar 2018	G8QI	376.51672727	396.07400000
Mar 2018	JPEIGLBL	790.13954545	807.95000000
Mar 2018	JPEIPLUS	814.19500000	836.13000000
Apr 2018	G8QI	383.20209524	396.07400000
Apr 2018	JPEIGLBL	790.66190476	807.95000000
Apr 2018	JPEIPLUS	815.77523810	836.13000000
May 2018	G8QI	379.92808696	396.07400000
May 2018	JPEIGLBL	772.81739130	807.95000000
May 2018	JPEIPLUS	794.29304348	836.13000000
Jun 2018	G8QI	385.03952381	396.07400000
Jun 2018	JPEIGLBL	766.36809524	807.95000000
Jun 2018	JPEIPLUS	786.19952381	836.13000000
Jul 2018	G8QI	390.81027273	396.07400000
Jul 2018	JPEIGLBL	777.43409091	807.95000000
Jul 2018	JPEIPLUS	797.31181818	836.13000000

[sunny_talwar]

May be this

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

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

[naveen341]

Awesome it work thanks Sunny

[sunny_talwar]

2 things

1) How is your own response "Awesome it work thanks Sunny" a helpful response

2) How is this a thread "Assumed Answer" if my response did work for you as per point number 1 above

I request you to fix the above two things as it helps future visitors to this thread to look for the right answers.

Best,

Sunny

[naveen341]

It was my bad i hit that button without knowingly.

Your response worked for me.

[sunny_talwar]

Then please fix it . If you don't know how... go through the thread below

Qlik Community Tip: Marking Replies as Correct or Helpful