Expression help please

[#1]

ID	TYPE	AMOUNT
1	RENT	400
1	RENT	200
1	DEP	300
1	DEP	300
2	TAX	100
2	TAX	200
2	RENT	400
2	RENT	100
	For each ID I have to get the max of amount of each type and then sum it..
	Here Ex: ID =1 ,Max(Rent) =400
		Max(Dep)= 300
	For ID =1,Amount is 700

Thank you very much .

[#2]

Try This:

In Script:

TABLE:

LOAD * INLINE

[ID,TYPE,AMOUNT

1,RENT,400

1,RENT,200

1,DEP,300

1,DEP,300

2,TAX,100

2,TAX,200

2,RENT,400

2,RENT,100];

NoConcatenate

TABLE2:

LOAD ID AS N_ID,

  TYPE AS N_TYPE,

  MAX(AMOUNT) AS N_AMOUNT

Resident TABLE

GROUP BY ID, TYPE;

Then, In Expression:

SUM(AGGR(SUM(N_AMOUNT),N_TYPE,N_ID))

Hope it helps!!

Regards,

[Digvijay_Singh · Accepted solution]

See attached - I have checked the total option in dimension limit tab for type dimension

[#4]

Hi pavana,Have a nice day.

I think this is helpful for you.

[#5]

I have below expression,

Max({<[Type]={'RENT'}>}[Amount of Expense])))    +

Max({<[Type]={'DEP'}>}[Amount of Expense])))  +

Max({<[Type]={'TAX'}>}[Amount of Expense]))) 

= 400+300+100 but since ID 1 doesn’t have TYPE = TAX It is giving me blank.Can I say if ID doesn’t have the match to any type then make  it 0.

if(Max({<[Type]={'RENT'}>}[Amount of Expense])))=null(),0, Max({<[Type]={'RENT'}>}[Amount of Expense])))   + if(Max({<[Type]={'DEP'}>}[Amount of Expense]))) = null(),0,Max({<[Type]={'DEP'}>}[Amount of Expense])))  +if(Max({<[Type]={'TAX'}>}[Amount of Expense])))   = null(),0,Max({<[Type]={'TAX'}>}[Amount of Expense]))))

[#6]

Hi Digvijay,Thank you very much ,your script did worked.I have to add additional 2 filters(In Bold) as below and it is not filter out based on that.Is there any other way I can put it ?

=Sum(IF([Expense Month-Year] >= MonthStart(Today()) ,

       IF([Expense Month-Year] <= MonthEnd(Today(), 12),

         Aggr(Max([Amount of Expense]*12),%SubspaceKey,[Expense Type]))))

[Digvijay_Singh]

You should try set expression instead of sum. Ensure both side of comparison are either month number or date to avoid any formatting issue. Try something like this -

=Sum({<[Expense Month-Year] ={">=$(=MonthStart(Today())) <= $(=MonthEnd(Today(), 12))"}>}

         Aggr(Max({<[Expense Month-Year] ={">=$(=MonthStart(Today()) )<= $(=MonthEnd(Today(), 12))"}>}[Amount of Expense]*12),%SubspaceKey,[Expense Type]))))

[#8]

Give me 0

[sunny_talwar]

Can you try this:

Fixed some extra spaces and some extra parenthesis at the end

=Sum({<[Expense Month-Year] ={">=$(=MonthStart(Today()))<=$(=MonthEnd(Today(), 12))"}>} Aggr(Max({<[Expense Month-Year] ={">=$(=MonthStart(Today()))<=$(=MonthEnd(Today(), 12))"}>} [Amount of Expense]*12), %SubspaceKey, [Expense Type]))

or this:

=Sum({<[Expense Month-Year] = {"$(='>=' & MonthStart(Today()) & '<=' & MonthEnd(Today(), 12))"}>} Aggr(Max({<[Expense Month-Year] = {"$(='>=' & MonthStart(Today()) & '<=' & MonthEnd(Today(), 12))"}>} [Amount of Expense]*12), %SubspaceKey, [Expense Type]))

[#10]

Below expression did work for me but now I have to add additional filters.Having a hard and thank you for your support.

sum(IF([Expense Month-Year] >= date(Today(),'MMM-YYYY') ,

     IF([Expense Month-Year] <= date(AddMonths(Today(),12),'MMM-YYYY'),

      Aggr(Max([Amount of Expense]*12),%SubspaceKey,[Expense Type]))))

Can I write something like below or is there a better way to write this as this is throwing me 0.

sum(IF([Expense Month-Year] >= date(Today(),'MMM-YYYY') ,

      IF([Expense Month-Year] <= date(AddMonths(Today(),12),'MMM-YYYY'),

          Aggr(if(pay_frequency='MONTHLY',(Max([Amount of Expense]*12,

                  if(pay_frequency='QUARTERLY',(Max([Amount of Expense]*4,

                  if(pay_frequency='ANNUALLY',(Max([Amount of Expense]*1,

                   if(pay_frequency='SEMI-ANNUALLY',(Max([Amount of Expense]*2)))))))))))),%SubspaceKey,[Expense Type]))))