topic Re: Expression help please in QlikView

Expression help please

Fri, 26 Feb 2016 13:46:27 GMT

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 .

Re: Expression help please

Fri, 26 Feb 2016 14:03:45 GMT

Try This:

In Script:

TABLE:

LOAD * INLINE

[ID,TYPE,AMOUNT

1,RENT,400

1,RENT,200

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,

Re: Expression help please

Digvijay_Singh — Fri, 26 Feb 2016 14:16:28 GMT

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

Re: Expression help please

Fri, 26 Feb 2016 14:19:07 GMT

Hi pavana,Have a nice day.

I think this is helpful for you.

Re: Expression help please

Fri, 26 Feb 2016 14:45:37 GMT

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

Re: Expression help please

Fri, 26 Feb 2016 15:06:21 GMT

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

Re: Expression help please

Digvijay_Singh — Fri, 26 Feb 2016 15:17:43 GMT

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

Re: Expression help please

Fri, 26 Feb 2016 15:23:24 GMT

Give me 0

Re: Expression help please

sunny_talwar — Fri, 26 Feb 2016 15:39:11 GMT

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

Re: Expression help please

Fri, 26 Feb 2016 16:56:55 GMT

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

Re: Expression help please

sunny_talwar — Fri, 26 Feb 2016 17:02:08 GMT

May be this:

Sum(

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

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

Aggr(

(Max([Amount of Expense]) * Pick(Match(pay_frequency, 'MONTHLY', 'QUARTERLY', 'ANNUALLY', 'SEMI-ANNUALLY'), 12, 4, 1, 2)),%SubspaceKey,[Expense Type]))))

Re: Expression help please

Mon, 29 Feb 2016 03:28:29 GMT

Thank you very much...it made wonders