topic Need help in QlikView

Need help

Thu, 19 Apr 2012 04:13:56 GMT

Dear All

My source as below.

CustomerID	Agreementid	DPD	AMT
100	C01	20	100
100	A02	30	200
100	B04	10	300
200	X101	180	500
200	Y102	30	200
200	Z104	60	60
300	M105	60	400
300	N106	180	500
300	O107	20	300

If one customer one Agreementid have DPD>180 i want to show all the Agreementids amt.

Ex:

CustomerID	Agreementid	DPD	AMT
200	X101	180	500
200	Y102	30	200
200	Z104	60	60
300	M105	60	400
300	N106	180	500
300	O107	20	300

Need help

sivarajs — Thu, 19 Apr 2012 05:33:01 GMT

Can you explain your reqiurement clearly

Need help

SunilChauhan — Thu, 19 Apr 2012 06:23:44 GMT

use below code

sum( if(DPD>=180,AMT))

hope this helps

Need help

Thu, 19 Apr 2012 06:27:07 GMT

it is giving Sum(Amt)=1000, but i want sum(Amt)=1960

Re: Need help

adhudson — Thu, 19 Apr 2012 07:07:32 GMT

Hi,

PFA.

I have used a flag field to identify the customer ids which has any one DPD equal to 180.

Check and let me know if you ve got the solution.

Regards

Andrew Hudson

Re: Need help

Michiel_QV_Fan — Thu, 19 Apr 2012 07:15:34 GMT

Talluru,

here is your solution in an expression:

sum({<customerid = p({$<dpd = {'180'}>}customerid)>}amt)

You can create a variable to replace the fixed value 180, like this:

sum({<customerid = p({$<dpd = {'$(talluru_value)'}>}customerid)>}amt)

use a slider to select the talluru_value

Re: Need help

flipside — Thu, 19 Apr 2012 08:25:09 GMT

Hi Talluru,

Another option would be to create a calculated dimension in a chart with this ...

=if(aggr(max(DPD),CustomerID)>=180,aggr(max(DPD),CustomerID))

So you have 3 dimensions (CustomerID, Agreementid and the above) then sum(AMT) as your expression. The calculated dimension sets to Null any value below 180, so you need to tick the box Suppress when Value is Null.

flipside

Need help

Thu, 19 Apr 2012 08:40:20 GMT

Here

in Dimension:

dimension 1: =aggr(if(sum(DPD) > 180, CustomerID),CustomerID) //check "Suppress When Value is Null"

dimension 2: Agreementid

dimension3: DPD

in Expression:

sum(AMT)

Re: Need help

v_iyyappan — Thu, 19 Apr 2012 09:09:40 GMT

Hi,

I attached sample QV file.

Hope its helps for u

Regards,

Iyyappan

Re: Need help

Michiel_QV_Fan — Thu, 19 Apr 2012 09:20:02 GMT

Please be aware that calculations in a dimension are inefficient compared to calculations in an expression, they take more time and resources.

And,

If the selection is done in the dimension then there are 2 different places to consider for the end result:

the limitation of result set in the dimension
the calculation in the expression

Re: Need help

flipside — Thu, 19 Apr 2012 09:45:54 GMT

Hi Michiel,

Yes, that's a fair point. In fact, if it was me I would probably do this in script anyway by appending the max(DPD) value as a new column and referencing it in a normal expression (apologies if anyone else has already suggested this in their attached examples).

Data:
load * inline [
CustomerID, Agreementid, DPD, AMT
100, C01, 20, 100
100, A02, 30, 200
100, B04, 10, 300
200, X101, 180, 500
200, Y102, 30, 200
200, Z104, 60, 60
300, M105, 60, 400
300, N106, 190, 500
300, O107, 20, 300 ];

left join (Data)
LOAD
CustomerID,
max(DPD) as maxDPD
resident Data
group by CustomerID;

Then use expression similar to this in the chart (the 180 can be swapped to a variable as in your solution) ..

=sum(AMT)*if(maxDPD>=180,1,0)

flipside