Re: month wise sum with dynamic filter - Qlik Community

Anonymous · ‎2014-04-07

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 · ‎2014-04-07

Here it is

MK_QSL · ‎2014-04-07

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 · ‎2014-04-08

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 · ‎2014-04-08

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 · ‎2014-04-08

I gave you an example, please let me know

senpradip007 · ‎2014-04-08

PFA.

nizamsha · ‎2014-04-08

PFA U Asked