Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
ugurarslan
Contributor

Sum total AR

Dear all,

I have a table with the following aging

Seller IDSuspend IndicatorCurrent1-30 days31-60 days61-90 days91-120 days121-150 days150-181 days181+ days
1Suspended $5.00 $67.00 $67.00 $-   $7.00 $74.00 $734.00 $-  
4Not Suspended $66.00 $7.00 $-   $7.00 $8.00 $-   $-   $73.00
7Suspended $7.00 $78.00 $8.00 $-   $-   $-   $-   $-  
90Not Suspended $-   $-   $-   $-   $-   $-   $-   $-  
660Not Suspended $7.00 $-   $6.00 $3.00 $3.00 $67.00 $7.00 $-  
3456Suspended $-   $8.00 $-   $-   $-   $4.00 $3.00 $7.00
5677Not Suspended $7.00 $8.00 $7.00 $-   $-   $-   $-   $-  
89877Suspended $8.00 $-   $-   $7.00 $7.00 $-   $-   $3.00
321234Not Suspended $-   $-   $-   $-   $-   $-   $-   $-  
445444Suspended $888.00 $-   $-   $8.00 $8.00 $-   $3.00 $-  
456778Not Suspended $-   $-   $5.00 $-   $-   $7.00 $7.00 $-  
7883983Suspended $87.00 $-   $-   $45.00 $8.00 $-   $3.00 $7.00
8498989Not Suspended $-   $85.00 $-   $-   $-   $-   $-   $-  
44929029Suspended $54.00 $-   $5.00 $-   $4.00 $-   $-   $7.00

In case I want to see the count of seller ID's who have an balance above 90 days and are suspended I use the following expression:

count({<[[Suspend Indicator]={'Suspended'},[AR Aging Bucket]={'91 - 120 days','121 - 150 days','151 - 180 days','181+ days'},[Seller ID]={"=sum([AR Amt USD])>0"}>}distinct [Seller ID])

Result is 6 seller ID's who are suspended and have a balance + 90 days.

Seller IDSuspend IndicatorCurrent1-30 days31-60 days61-90 days91-120 days121-150 days150-181 days181+ days
1Suspended $5.00 $67.00 $67.00 $-   $7.00 $74.00 $734.00 $-  
3456Suspended $-   $8.00 $-   $-   $-   $4.00 $3.00 $7.00
89877Suspended $8.00 $-   $-   $7.00 $7.00 $-   $-   $3.00
445444Suspended $888.00 $-   $-   $8.00 $8.00 $-   $3.00 $-  
7883983Suspended $87.00 $-   $-   $45.00 $8.00 $-   $3.00 $7.00
44929029Suspended $54.00 $-   $5.00 $-   $4.00 $-   $-   $7.00

I have the following questions.

Now I want to get the total balance for only these customers. Result Should be $ 2.128,- (please see orange marked in below table).

Seller IDSuspend IndicatorCurrent1-30 days31-60 days61-90 days91-120 days121-150 days150-181 days181+ days
1Suspended $5.00 $67.00 $67.00 $-   $7.00 $74.00 $734.00 $-  
4Not Suspended $66.00 $7.00 $-   $7.00 $8.00 $-   $-   $73.00
7Suspended $7.00 $78.00 $8.00 $-   $-   $-   $-   $-  
90Not Suspended $-   $-   $-   $-   $-   $-   $-   $-  
660Not Suspended $7.00 $-   $6.00 $3.00 $3.00 $67.00 $7.00 $-  
3456Suspended $-   $8.00 $-   $-   $-   $4.00 $3.00 $7.00
5677Not Suspended $7.00 $8.00 $7.00 $-   $-   $-   $-   $-  
89877Suspended $8.00 $-   $-   $7.00 $7.00 $-   $-   $3.00
321234Not Suspended $-   $-   $-   $-   $-   $-   $-   $-  
445444Suspended $888.00 $-   $-   $8.00 $8.00 $-   $3.00 $-  
456778Not Suspended $-   $-   $5.00 $-   $-   $7.00 $7.00 $-  
7883983Suspended $87.00 $-   $-   $45.00 $8.00 $-   $3.00 $7.00
8498989Not Suspended $-   $85.00 $-   $-   $-   $-   $-   $-  
44929029Suspended $54.00 $-   $5.00 $-   $4.00 $-   $-   $7.00

Can you please help me?

Thanks

Ugur

2 Replies
ogautier62
Valued Contributor II

Re: Sum total AR

Hi,

I think you could do like this :

sum({<SellerId = P(' your expression for the count')>} sales)

regards

Digvijay_Singh
Honored Contributor III

Re: Sum total AR

This works -

Sum({<[Seller ID]={"=Sum({<[Suspend Indicator]={'Suspended'},[AR Aging Bucket]={'91-120 days','121-150 days','151-180 days','181+ days'}>}[AR Amt USD])>0"}>}[AR Amt USD])