Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ugurarslan
New 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])

Community Browser