Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution.
**READ ALL ABOUT IT!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Help with aggr dimension

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

educastri83

Creator

2018-10-30
04:40 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Help with aggr dimension

Hi! community.

I have the following problem that I would like to solve

I have a KPi that measures the bills that have had a discount greater than 80% and that also the total of the invoice is equal to 1 dollar. In a text object I can represent it in the following way:

**= Count (DISTINCT Aggr (If ((sum ({<CHARGE_TYPE_DESC = {'Credit'}>} CHARGE_NET_AMOUNT) **

*+*

*sum ({<CHARGE_TYPE_DESC = {'Discount'}>} CHARGE_NET_AMOUNT) )*

*/*

**sum ({<CHARGE_TYPE_DESC = {'Debit'}>} CHARGE_NET_AMOUNT)> = 0.8 **

I have a KPi that measures the bills that have had a discount greater than 80% and that also the total of the invoice is equal to 1 dollar. In a text object I can represent it in the following way:

The above works well in a text object, but now I need to

I think it is using AGGR in the dimension but the truth is not my strong

thanks for your help

CC:

1,028 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2018-10-31
07:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

For single value the total qualifier should work...

**=Count (TOTAL DISTINCT Aggr(If((Sum({<CHARGE_TYPE_DESC = {'Credit'}>} CHARGE_NET_AMOUNT) + Sum({<CHARGE_TYPE_DESC = {'Discount'}>} CHARGE_NET_AMOUNT))**

**/Sum({<CHARGE_TYPE_DESC = {'Debit'}>} CHARGE_NET_AMOUNT) >= 0.8 and Sum(CHARGE_NET_AMOUNT) >= 1, INVOICE_KEY), INVOICE_KEY))**

But for multiple values, which field or fields would you want the count on?

5 Replies

sunny_talwar

MVP

2018-10-30
06:27 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

What exactly are you hoping to see in your dimension? A single value or bunch of values?

845 Views

educastri83

Creator

2018-10-30
08:03 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

In this case a single value,

but I would appreciate if you guide me for when it comes to a bunch of values.

Thanks

845 Views

sunny_talwar

MVP

2018-10-31
07:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

For single value the total qualifier should work...

**=Count (TOTAL DISTINCT Aggr(If((Sum({<CHARGE_TYPE_DESC = {'Credit'}>} CHARGE_NET_AMOUNT) + Sum({<CHARGE_TYPE_DESC = {'Discount'}>} CHARGE_NET_AMOUNT))**

**/Sum({<CHARGE_TYPE_DESC = {'Debit'}>} CHARGE_NET_AMOUNT) >= 0.8 and Sum(CHARGE_NET_AMOUNT) >= 1, INVOICE_KEY), INVOICE_KEY))**

But for multiple values, which field or fields would you want the count on?

educastri83

Creator

2018-10-31
05:34 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Sunny

The operation

**sum ({<CHARGE_TYPE_DESC = {'Credit'}>} CHARGE_NET_AMOUNT) **

*+*

*sum ({<CHARGE_TYPE_DESC = {'Discount'}>} CHARGE_NET_AMOUNT) )*

*/*

**sum ({<CHARGE_TYPE_DESC = {'Debit'}>} CHARGE_NET_AMOUNT)**

it can be too between:

>0.01 - < 0.20

> 0.20 and <0.40

> 0.40 and <0.80

> 0.80 (its okey, already )

= 100

**Sum(CHARGE_NET_AMOUNT) >= 1, INVOICE_KEY don't change **it stays the same

845 Views

educastri83

Creator

2018-11-03
10:02 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Sunny,

I have this other example:

I got a bar char that contains 7 expressions, each one for a brunch of values. The Bar chart dont have a dimenssion and really,,, i don't like it.. I would like to do better

The detail for each bar it is:

**1 Expression: 70 - 75**

=Count(DISTINCT Aggr(If(Sum(

CHARGE_NET_AMOUNT) <= 74.9 AND Sum(CHARGE_NET_AMOUNT) >= 70 , INVOICE_KEY), INVOICE_KEY))

**2. Expression: 60-69**

=Count(DISTINCT Aggr(If(Sum(CHARGE_NET_AMOUNT) <= 69.9 AND Sum(CHARGE_NET_AMOUNT) >= 60 , INVOICE_KEY), INVOICE_KEY))

**3. Expression: 50-59**

=Count(DISTINCT Aggr(If(Sum( CHARGE_NET_AMOUNT) <= 59.9 AND Sum(CHARGE_NET_AMOUNT) >= 50 , INVOICE_KEY), INVOICE_KEY))

..And so on until reach the last expression.

The Bar chart i've created looks like:

I wanted to create an adequate dimension to simplify brunch of values expressions and can counting the numbers of Invoice..

from now thank you very much if you can help me stalwar1

845 Views