Set analysis - Aggr or anyother option?

Fri, 06 May 2011 23:49:02 GMT

I have 2 Charts(You can visualize the attached image) as follows -

First - Refer the image for Feature chart

Dimension

l_calendar_monthend --- date

t_calendar_monthend --- date

Expression-

count(DISTINCT If(t_transaction_date <= t_calendar_monthend and t_loss_date <= l_calendar_monthend and t_calendar_monthend >= l_calendar_monthend,t_claim_feature))

Second - Refer the atatched image for Loss chart

t_loss_monthend --- date
t_transaction_monthend --- date

Expression-

rangesum(Before(total sum(t_Loss_amt),0,120)),t_loss_monthend,t_transaction_monthend

I was trying to get a third chart as follows-

Dimension

t_loss_monthend --- date
t_transaction_monthend --- date

& combined expression(Basically I want to divide each cell from 2 chart with values from 1st cell chart) -

Aggr(rangesum(Before(total sum(t_Loss_amt),0,120)),t_loss_monthend,t_transaction_monthend)
/
Aggr(
count(DISTINCT If(t_transaction_date <= t_calendar_monthend and t_loss_date <= l_calendar_monthend and t_calendar_monthend >= l_calendar_monthend,t_claim_feature)),
l_calendar_monthend,
t_calendar_monthend)

but it does not work. I also tried to use the VB Macro to get the calculations into a vb array but again was unsucessfull while loading to table using

/*-----------------------------

set obj = ActiveDocument.GetSheetObject("TB01")

obj.SetInputFieldCell 1,1,"999" 'set 2nd row value in 2nd column to 999

***/////

Can someone pease assist -

Re: Set analysis - Aggr or anyother option?

IAMDV — Sat, 07 May 2011 07:40:08 GMT

Hi,

I would use Dollar Sign expansion with Set Analysis. I mean instead of creating a VB Script, you can use a dollar sign expansion as the User Defined Function. And then you can call first function & second function...

Please refer to the below post. I hope it helps you...

http://qlikviewmaven.blogspot.com/

Good luck!

Cheers - DV

topic Set analysis - Aggr or anyother option? in QlikView

Set analysis - Aggr or anyother option?

Re: Set analysis - Aggr or anyother option?