Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
limingthefirst
New Contributor III

Sum into a Text Object - How to use AGGR after fliter

Hello,

I have a straight table (see screenshot).I'm trying to get the sum of rows of the Spend into a text object.

And the 1/2/3/4 Vendor(s) is Dimensions with extension below:


=if(aggr(Count(DISTINCT Vendor_Code), Material_Code)=1,

'1 Vendor',aggr(Count(DISTINCT Vendor_Code), Material_Code)&' Vendors')


20171107203339.png

Now when I sum the Spend of 1 Vendor into Text Object, the result is incorrect.

=sum(if(aggr(Count(DISTINCT Vendor_Code), Material_Code)=1,Delivery_Amount))

or try this extension is incorrect too:

=sum(aggr(if(aggr(Count(DISTINCT Vendor_Code), Material_Code)=1,Delivery_Amount), Material_Code))


I want to sum the Spend of the material_code has only one vendor_code:

Can anybody help me to correct the extension ?


Thank you

Ming

1 Solution

Accepted Solutions

Re: Sum into a Text Object - How to use AGGR after fliter

May be this

Sum({<Material_Code = {"=Count(DISTINCT Vendor_Code) = 1"}>}Delivery_Amount)

5 Replies

Re: Sum into a Text Object - How to use AGGR after fliter

Try this?

=Sum(If(Aggr(Count({<Vendor_Code = {'1 Vendor'}>} DISTINCT Vendor_Code), Material_Code)=1,Delivery_Amount))


Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Sum into a Text Object - How to use AGGR after fliter

May be this

Sum({<Material_Code = {"=Count(DISTINCT Vendor_Code) = 1"}>}Delivery_Amount)

limingthefirst
New Contributor III

Re: Sum into a Text Object - How to use AGGR after fliter

Hi, thanks for your response,

The '1 Vendor' is not the value of Vendor_code.

it Calculated with

=if(aggr(Count(DISTINCT Vendor_Code), Material_Code)=1,

'1 Vendor',aggr(Count(DISTINCT Vendor_Code), Material_Code)&' Vendors')

limingthefirst
New Contributor III

Re: Sum into a Text Object - How to use AGGR after fliter

It's working well, Thank you!

Re: Sum into a Text Object - How to use AGGR after fliter

Then, Sunny's reply make sense

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)