topic Re: Filter on sum in QlikView

Filter on sum

Fri, 23 Aug 2013 00:30:44 GMT

Hello Everyone,

I recently started using qlikview a week ago and need some help in implementing a functionality.

I am working with accounting data and the simple table structure is as follows:

4 Main fields: @Journal ID, Line Item , Account No and Amount

one Journal ID has multiple line items and each line item has one account and one Amount associated with it.

The functionality I am trying to implement is as follows:

Accept a number from user VarX (was able to do this) and display all line items where Sum( Amount by Line items) is less than or equal to VarX.

Rough Table Structure:

Journal ID, Line Item, Account No, Amt

JID1,LItem1, Acct1, $xx

JID1, LItem2, Acct2, $xx

JID1, LItem3, Acct3, $xx

JID2, LItem1, Acct1, $xx

JID2, Litem4, Acct4, $xx

Also, in a raw data I have Journal ID, Account no and Amount in one table while Account no and Line Item in different table.

I cannot figure out how to implement this functionality, I understand I will have to use group by( aggregation ) functionality here but somehow I cannot find a proper way to put everything together to obtain the required functionality. Any help will be appreciated.

Thanks in advance!

Re: Filter on sum

somenathroy — Fri, 23 Aug 2013 04:06:30 GMT

PFA...

Regards,

Som

Re: Filter on sum

Kushal_Chawda — Fri, 23 Aug 2013 04:12:24 GMT

see the attached one.

is it what you are looking for?

Re: Filter on sum

Alejandro_Hernández — Fri, 23 Aug 2013 04:12:56 GMT

see attached file

Re: Filter on sum

Fri, 23 Aug 2013 14:48:46 GMT

Hello Some+Nath Roy,

This is very close to what I need, thanks for the qvw. But like I said, I do not have the 'sum by line item' field in original table and I have ~2 M records. Is there a way to apply this filter logic and display only the line items where the 'sum by line Item' is less that user input variable.

Re: Filter on sum

Gysbert_Wassenaar — Fri, 23 Aug 2013 15:03:23 GMT

Perhaps this: sum({<[Line Item]={'=sum(Amount)<=$(VarX)'}>}Amount)