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

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Aggr Expressions NOT Working when using ValueList(...

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

sudhakaran_abha

Creator

2017-10-02
03:44 PM

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

Aggr Expressions NOT Working when using ValueList() to create Synthetic Dimension

I have attached a sample Qlik Sense App that contains a straight table with Custom Dimensions and Expressions.

Here you would see that the second expression DOES NOT generate any output, even though it is the same expression. The only thing that stands out to me is that, the second instance of AGGR in such scenario doesn't work.

**Front End Table**

**Table Expression Code**

**Dimension:**

=ValueList('Exp1', 'Exp2', 'Exp3')

**Expression:**

Pick(Match(ValueList('Exp1', 'Exp2', 'Exp3'), 'Exp1', 'Exp2', 'Exp3')

, Sum(Aggr(Sum(Expression1), Dim1))

, Sum(Aggr(Sum(Expression1), Dim1))

, Sum(Expression1)

)

Any help is appreciated.

3,091 Views

1 Solution

Accepted Solutions

sudhakaran_abha

Creator

2017-10-05
10:50 AM

Author

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

Here is the solution that worked for me:

Create island tables with the Dummy Dimensions in the Data Model (instead of using ValueList() function to create the synthetic dimensions) and include the Dimension Field in grouping.

**Dimension**

KPI_Dimension:

Load * INLINE [

KPI_Metric

Exp1

Exp2

Exp3

];

**Expression:**

Pick(Match(ValueList('Exp1', 'Exp2', 'Exp3'), 'Exp1', 'Exp2', 'Exp3')

, Sum(Aggr(Sum(Expression1), Dim1, KPI_Metric))

, Sum(Aggr(Sum(Expression1), Dim1, KPI_Metric))

, Sum(Expression1)

)

This solves the problem of creating unique aggregations, which is not possible using ValueList(), where the Qlik Engine doesn't understand each dummy value needs a distinct grouping.

2,827 Views

6 Replies

swuehl

MVP

2017-10-02
03:48 PM

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

Try

Pick(Match(ValueList('Exp1', 'Exp2', 'Exp3'), 'Exp1', 'Exp2', 'Exp3')

, Sum(TOTAL Aggr( Sum(Expression1), Dim1))

//, Sum(Aggr(Sum(Expression1), Dim1))

, Sum(TOTAL Aggr( Sum(Expression1), Dim1))

, Sum(Expression1)

)

2,827 Views

sunny_talwar

MVP

2017-10-02
03:48 PM

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

This is a known issue... look here:

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies

sudhakaran_abha

Creator

2017-10-05
10:50 AM

Author

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

Here is the solution that worked for me:

Create island tables with the Dummy Dimensions in the Data Model (instead of using ValueList() function to create the synthetic dimensions) and include the Dimension Field in grouping.

**Dimension**

KPI_Dimension:

Load * INLINE [

KPI_Metric

Exp1

Exp2

Exp3

];

**Expression:**

Pick(Match(ValueList('Exp1', 'Exp2', 'Exp3'), 'Exp1', 'Exp2', 'Exp3')

, Sum(Aggr(Sum(Expression1), Dim1, KPI_Metric))

, Sum(Aggr(Sum(Expression1), Dim1, KPI_Metric))

, Sum(Expression1)

)

This solves the problem of creating unique aggregations, which is not possible using ValueList(), where the Qlik Engine doesn't understand each dummy value needs a distinct grouping.

2,828 Views

Ribeiro

Specialist

2020-04-26
09:57 AM

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

Any idea how to get this set analysis right

the first line works the others are zero

PICK( Match(ORDEM1,

$(=Concat({<ORDEM1 -= {''}>}DISTINCT chr(39) & ORDEM1 & chr(39),',',ORDEM1))

),

sum({<Ano={$(=Max(Ano))},[Mes]={'Fev'}>}Aggr($(vExpressao1), Ano,Mes)),sum({<Ano={$(=Max(Ano))},[Mes]={'Fev'}>}Aggr($(vExpressao2), Ano,Mes)),

sum({<Ano={$(=Max(Ano))},[Mes]={'Fev'}>}Aggr($(vExpressao3), Ano,Mes)),sum({<Ano={$(=Max(Ano))},[Mes]={'Fev'}>}Aggr($(vExpressao4), Ano,Mes))

)

Neves

2,064 Views

Ribeiro

Specialist

2020-04-26
10:00 AM

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

Sunny,

the first line works the others are zero

set analysis below

Neves

2,063 Views

SujanJoeJacob

Contributor II

2021-03-12
09:02 AM

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

Hi,

What should come in Expression1?

And where should we define it? Data load or write it as expression? **Dimension**

KPI_Dimension:

Load * INLINE [

KPI_Metric

GMV

Discounts

Cancellation

Return

NetSales

];

My GMV Expression : (Sum(Aggr(Only(line_items_price), [order_id]))).

Not sure how to include this in

**Pick(Match(ValueList('Exp1', 'Exp2', 'Exp3'), 'Exp1', 'Exp2', 'Exp3')**

**, Sum(Aggr(Sum(Expression1), Dim1, KPI_Metric))**

**, Sum(Aggr(Sum(Expression1), Dim1, KPI_Metric))**

**, Sum(Expression1)**

* )*to make it work.