Need help with a set analysis calculation

[manishnarang]

Hi,

I need to know the PIDs who paid more than 499 in their first visit.

I have the below sample data:

PID	VisitDate	BillPaid
P2	2-Jan-14	600
P1	1-Jan-15	400
P2	3-Jan-15	1000
P1	10-Mar-15	500
P3	1-Apr-15	200
P3	4-Apr-15	500

and this has to be a calculated dimension, as i have some other columns which i want to calculate for these PIDs.

Thanks,

Best,

Manish

[#2]

Hi..

Manish Kumar

1.create straight and check BillPaid values>499  .

2.to take dimension PID .

3.expression :sum(BillPaid>=499) or sum(BillPaid>499)

4.Bar chart also sum(BillPaid>=499)

here take two dimension also.

try once ...

helpful

Thanks

sreenu

[manishnarang]

close but not exactly what i need.

its who paid more than 499 in their first visit... so first visit (min visit date) is also to be considered.

any idea on that ?

[arulsettu]

hi try this

if(sum(BillPaid)>499,min(VisitDate))

check your date format

[sunny_talwar]

May be this, PFA

Created a flag in the script for minimum date for each PID to make the calculations easier on the front end:

Table:

LOAD PID,

     Date#(VisitDate, 'D-MMM-YY') as VisitDate,

     BillPaid

FROM

[https://community.qlik.com/thread/158873]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD PID,

  Min(VisitDate) as VisitDate,

  1 as Flag

Resident Table

Group By PID;

Best,

Sunny