Last monthly amount for each quarter using set analysis

[deblina_rai]

Input DAta:

Product type	Asset Year	Asset  Quarter	Asset Month	Amount
MF	2019	Q1	JAn	39M
MF	2019	Q1	Feb	33M
MF	2019	Q1	Mar	40M
MF	2019	Q2	Apr	50M
MF	2019	Q2	MAy	67M
MF	2019	Q2	Jun	41M

Out put data: I need  latest month amount for each quarter while selecting two month like below

Product type	Asset Year	Asset  Quarter	Amount
MF	2019	Q1	40M
MF	2019	Q2	41M

[sunny_talwar]

Forgot to add the square brackets around the field names. Anyways, are you able to share an example file to check what you have?

[deblina_rai]

Actually there is no such example.

If users pulls any date dimension into a chart, the AUM Amt master measure should give us the latest asset for that.

For the quarter chart – it should be the latest month AUM Amt in each quarter.

For the yearly chart – it should be the latest month AUM Amt in each year and so on.

For example, in the very first  chart  that  i shared,

Q3 19 – should give us the asset from Jul 19 (As  latest data present for July)

Q2 19 – should give us the asset from Jun 19

Q1 19 – should give us the asset from Mar 19

Q4 18 – should give us the asset from Dec 18 and so on.

Same for yearly as well – from the latest month in each year.

Here i used Product Type , Asset year , Asset Quarter in my example, but user can select Firm  Dimension Instead of Product or Firm  along with Representative as Dimension . So  group by  level is not known to us. Only we need to create master measure in  such a way so that  it can be used  with any dimesion but will give latest month amount for the selected quarter or year.

[sunny_talwar]

I understand what you mean... I just don't have time to create a sample and test it out... either you have to provide a sample where I can test this.... or you will have to work on this on your own or somebody else can offer there help.

Best,
Sunny

[deblina_rai]

Thanks a lot for your help and Time Sunny.
I am using that example itself that i shared at the top.
Input Data:
Product type Asset Year Asset Quarter Asset Month Amount
MF 2019 Q1 JAn 39M
MF 2019 Q1 Feb 33M
MF 2019 Q1 Mar 40M
MF 2019 Q2 Apr 50M
MF 2019 Q2 MAy 67M
MF 2019 Q2 Jun 41M
IN 2019 Q1 JAn 55M
IN 2019 Q1 Feb 67M
IN 2019 Q1 Mar 65M
IN 2019 Q2 Apr 87M
IN 2019 Q2 MAy 85M
IN 2019 Q2 Jun 78M

Output:

Product type Asset Year Asset Quarter Amount
MF 2019 Q1 40M
MF 2019 Q2 41M
IN 2019 Q1 65M
IN 2019 Q2 78M

MAy be i have to try using backend script

[sunny_talwar]

This expression works for upto 3 dimensions... but you can very easily change it to 10 or 20

=FirstSortedValue(
   Aggr(
     Sum(Amount)
   , [$(=GetObjectField(0))], [$(=If(Len(Trim(GetObjectField(1))) = 0, GetObjectField(0), GetObjectField(1)))], [$(=If(Len(Trim(GetObjectField(2))) = 0, GetObjectField(0), GetObjectField(2)))], [Asset Month]),
  -Aggr(
     [Asset Month]
   , [$(=GetObjectField(0))], [$(=If(Len(Trim(GetObjectField(1))) = 0, GetObjectField(0), GetObjectField(1)))], [$(=If(Len(Trim(GetObjectField(2))) = 0, GetObjectField(0), GetObjectField(2)))], [Asset Month])
)

When I have two dimensions

When I have three dimensions

[deblina_rai]

Thanks a lot for your options. But as i am creating Master Measure  and i   dont know how many dimension user will use so i cant go with this way.

I have to use may be in scripting or any other way. If any other idea is there please let me know.

[sunny_talwar]

There has to be a upper limit to the number of dimensions a user can select, right? 5, 10, 20, 50? You can create your expression to address for the maximum number of dimensions a user can select. If you still don't think this can work, then I guess I am not sure of another way.

All the best,
Sunny

[deblina_rai]

num(sum({<[Asset Month]={"=[Asset Month]=aggr(nodistinct max([Asset Month]),[Asset Quarter])"}>}[AUM Amt]),'#,##0')

This one worked.