topic Re: Need help with a set analysis calculation in QlikView

Need help with a set analysis calculation

manishnarang — Sun, 05 Apr 2015 10:12:52 GMT

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

Re: Need help with a set analysis calculation

Sun, 05 Apr 2015 11:15:21 GMT

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

Re: Need help with a set analysis calculation

manishnarang — Sun, 05 Apr 2015 11:37:19 GMT

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 ?

Re: Need help with a set analysis calculation

arulsettu — Sun, 05 Apr 2015 11:44:27 GMT

hi try this

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

check your date format

Re: Need help with a set analysis calculation

sunny_talwar — Sun, 05 Apr 2015 12:56:51 GMT

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