month wise sum with dynamic filter

[Anonymous]

Hi Friends,

Please help me on the following problem.

From transaction data I want to calculate month wise amt from the following data

trandate           account      dpd      amt

01/01/2014      001           30           1

01/01/2014      002           41            200

01/01/2014      003           21           2

01/01/2014      004           51           400

01/01/2014      007           31           500

01/02/2014      001           25           100

01/02/2014      002           41           200

01/02/2014      003           31           300

01/02/2014      004           51           400

01/02/2014      005           31           500

01/03/2014      001           25           100

01/03/2014      002           41           200

01/03/2014      004           51           400

with conditions

For

Jan-2014: considers account having dpd>30 for trandate 01/01/2014

Feb-2014: considers account having dpd>30 for trandate period 01/01/2014 to 01/02/2014

Mar-2014: considers account having dpd>30 for trandate period 01/01/2014 to 01/03/2014

so basically the output will be

Month           amt

Jan-2014      1100

Feb-2014      1400

Mar-2014      600

Also attaching the test qlikview application.

Regards,

Chandan Chakraborty

[alexandros17]

Here it is

[MK_QSL]

Temp:

Load *, Month(trandate) as Month Inline

[

  trandate,    account, dpd, amt

  01/01/2014,  001,     30,    1

  01/01/2014,  002,     41,  200

  01/01/2014,  003,     21,    2

  01/01/2014,  004,     51,  400

  01/01/2014,  007,     31,  500

  01/02/2014,  001,     25,  100

  01/02/2014,  002,     41,  200

  01/02/2014,  003,     31,  300

  01/02/2014,  004,     51,  400

  01/02/2014,  005,     31,  500

  01/03/2014,  001,     25,  100

  01/03/2014,  002,     41,  200

  01/03/2014,  004,     51,  400

];

Join

Load

  Month,

  SUM(amt) as TotalMonthlyAmt

Resident Temp

Where dpd > 30

Group By Month;

==================

Now create a straight table

Dimension = Month

Expression = SUM(TotalMonthlyAmt)

=======================

Another option...

Just load

Temp:

Load *, Month(trandate) as Month Inline

[

  trandate,    account, dpd, amt

  01/01/2014,  001,     30,    1

  01/01/2014,  002,     41,  200

  01/01/2014,  003,     21,    2

  01/01/2014,  004,     51,  400

  01/01/2014,  007,     31,  500

  01/02/2014,  001,     25,  100

  01/02/2014,  002,     41,  200

  01/02/2014,  003,     31,  300

  01/02/2014,  004,     51,  400

  01/02/2014,  005,     31,  500

  01/03/2014,  001,     25,  100

  01/03/2014,  002,     41,  200

  01/03/2014,  004,     51,  400

];

Now create a straight table

Dimension = Month

Expression = SUM({<dpd = {'>30'}>}amt)

[Anonymous]

Thank you for your reply.

I think you have missed out the conditions.More clearly saying,

for Feb-2014

step 1:

set of accounts having dpd>30 for the period 01/01/2014 to 01/02/2014.

From here a list of accounts will be returned

step 2:

now consider only those accounts for 01/01/2014 which are available in accounts returned by step 1.

similarly for Mar-2014 in step 1 it will make list of accounts for the period 01/01/2014 to 01/03/2014.

so step 1 is dynamic the trandate period will be from starting trandate to for the trandate.

[MK_QSL]

Temp:

Load *, Month(trandate) as Month, NUM(Month(trandate)) as MonthNO Inline

[

  trandate,    account, dpd, amt

  01/01/2014,  001,     30,    1

  01/01/2014,  002,     41,  200

  01/01/2014,  003,     21,    2

  01/01/2014,  004,     51,  400

  01/01/2014,  007,     31,  500

  01/02/2014,  001,     25,  100

  01/02/2014,  002,     41,  200

  01/02/2014,  003,     31,  300

  01/02/2014,  004,     51,  400

  01/02/2014,  005,     31,  500

  01/03/2014,  001,     25,  100

  01/03/2014,  002,     41,  200

  01/03/2014,  004,     51,  400

];

NoConcatenate

Temp2:

Load

  Month,

  Sum(amt) as TotalMonthlyAmt

Resident Temp

Where dpd > 30

Group By Month;

Final:

Load

  Month,

  RangeSum(Peek('Total'), SUM(TotalMonthlyAmt)) as Total

Resident Temp2

Group By Month

Order By Month;

Drop Tables Temp, Temp2;

================================

Now create a straight table

Dimension = Month

Expression = SUM(Total)

[alexandros17]

I gave you an example, please let me know

[senpradip007]

PFA.

[nizamsha]

PFA U Asked