Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ugurarslan
Creator
Creator

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

0 Replies