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

Tags (1)
Community Browser