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

Discussion Board for collaboration related to QlikView App Development.

Announcements

CUSTOMERS ONLY: Now accepting **customer** applications for the 2023 Luminary Program: **SUBMIT NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Complex expression doesn't allow to open qvw w...

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

adriano_stefane

Partner - Contributor

2017-11-14
06:02 AM

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

Complex expression doesn't allow to open qvw with many data

Dear QV experts,

I've a *.qvw document with a line-chart, where we've 3 dimension fields and 1 complex expression, like:

**Dimension**s

- contentid (Enable Conditional =0)
- consumption (Enable Conditional =0)
- =Aggr(num(Rank(Sum({<contentid=>} consumption),4)), contentid)

**Expression**

- =2*((Rangesum(below(TOTAL (Aggr(num(Rank(Sum({<contentId=>} consumption),4)),contentid)*(sum(consumption)/sum(TOTAL consumption))),0,max(TOTAL(Aggr(num(Rank(Sum({<contentid=>} consumption),4)),contentid))))))*(Rangesum(below(TOTAL (1/(Rangesum(below(TOTAL sum(consumption/sum(TOTAL consumption)),0,max(TOTAL (Aggr(num(Rank(Sum({<contentid=>} consumption),4)),contentid))))))),0,max(TOTAL(Aggr(num(Rank(Sum({<contentid=>} consumption),4)),contentid)))))))-1

The expression calculates the effective catalog size (ECS). It is a metric that describes how spread viewing is across the items in our catalog. If most viewing comes from a single video, it will be close to 1. If all videos generate the same amount of viewing, it is close to the number of videos in the catalog. Otherwise it is somewhere in between.

In test environment, *.qvw document working fine, refresh takes 30sec, app size is 300KB and contains 7.000 rows. Into production environment when open the document, appear an error popup with message: "there was a problem sending the command to the program". Here, refresh takes 2min, app size is 20MB and contains 7.000.000 rows.

Probably expression is much complex to be calculate on the fly, but unfortunately can't be calculated a backend, because data depends by selection on Calendar fields. As default, expression is calculated for a single day.

Additional info:

- i'm using QlikView 12.10 SR7
- I can't share my *.qvw document
- HW specs, for Test 4 processors * 2Ghz and 64GB RAM, for Prod 16 processors * 2Ghz and 128GB RAM
- Attached, a sample data

Thanks in advance.

3 Replies

marcus_sommer

MVP & Luminary

2017-11-15
08:03 AM

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

I'm not surprised that this is quite slow to uncalculable by so many aggr and TOTAL within the interrecord-function below. I have the feeling that not all of the aggr, below and TOTAL are necessary and it could be simplified in some way.

I suggest that you make a re-start with this task beginning with rather simple sum/count expressions and if they return the expected results and needs to consolidated on step more if there are simpler solutions as just using aggr - especially by including some script pre-calculations.

- Marcus

402 Views

adriano_stefane

Partner - Contributor

2017-11-15
09:08 AM

Author

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

Hi Marcus,

thanks for your feedback, I've done as you said and below the best way of my review:

=2*((Rangesum(below((Aggr(num(Rank(Sum(consumption),4)),contentid)*(sum(consumption)/sum(TOTAL consumption))),0,max(TOTAL(Aggr(num(Rank(Sum(consumption),4)),contentid))))))*(Rangesum(below((1/(Rangesum(below(TOTAL sum(consumption/sum(TOTAL consumption)),0,max(TOTAL (Aggr(num(Rank(Sum(consumption),4)),contentid))))))),0,max(TOTAL(Aggr(num(Rank(Sum(consumption),4)),contentid)))))))-1

but nothing is changed. I've also splitted the entire expression into some variables declared on Variables Overview like this:

**MaxRank** =max(TOTAL(aggr(num(rank(sum(consumption),4)),contentid)))

**k1** =aggr(num(rank(sum(consumption),4)),contentid)*(sum(consumption)/sum(TOTAL consumption))

**k2** =1/(rangesum(below(TOTAL sum(consumption/sum(TOTAL consumption)),0,max(TOTAL (aggr(num(rank(sum(consumption),4)),contentid))))))

**ECS** =2*((rangesum(below($(k1),0,$(MaxRank))))*(rangesum(below($(k2),0,$(MaxRank)))))-1

but nothing is changed.

If you are interested, I've attached an excel file with calculation and for your reference you can find formula at the following link Appendix section..

Many thanks

402 Views

marcus_sommer

MVP & Luminary

2017-11-15
11:36 AM

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

To separate expressions or parts of it in variables is definitely useful to improve the readability and maintainability but it won't increase the performance (unless it leads to some precalculation and not an expression is returned else a value) because the executed expression itself didn't change.

I meant to check if you really need all the aggr-functions, see:

When should the Aggr() function NOT be used?

- Marcus

402 Views