Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression help please

    IDTYPEAMOUNT
1RENT400
1RENT200
1DEP300
1DEP300
2TAX100
2TAX200
2RENT400
2RENT100
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 .

1 Solution

Accepted Solutions
Digvijay_Singh

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

Capture.JPG

View solution in original post

11 Replies
Not applicable
Author

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

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

Capture.JPG

Not applicable
Author

Hi pavana,Have a nice day.

I think this is helpful for you.

Untitled.png

Not applicable
Author

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]))))

Not applicable
Author

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]))))

Not applicable
Author

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]))

Not applicable
Author

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]))))