topic Re: Object calulation way too long!!! in QlikView

Object calulation way too long!!!

sibrulotte — Mon, 29 Jul 2013 18:00:51 GMT

Hi,

so I was told it'd be quicker to load all the data from our warehouse, instead of selecting and posing the condition

Where

GLDetail.Annee_fiscale>=2009;

Thus only loading 5 years.

Load time is quicker without that condition, but object calculations are a pain.

So, how do I use the calculation condition in my objects (pivots) to limit the calculations done to only

Where

GLDetail.Annee_fiscale = AnnCourBilan

AnnCourBilan being a variable set as the year I'm looking at.

Any help is appreciated.

Thanks

Simon

I've tried alot of combinations and still can't get to my goal

Re: Object calulation way too long!!!

jpapador — Mon, 29 Jul 2013 18:05:32 GMT

In all of the expressions on the chart use set analysis. Example:

Sum({$<GLDetail.Annee_fiscale={'AnnCourBilan'}>}Sales)

You would replace the Sum with whatever function you are trying to do and replace sales with whatever field you are calculating.

Re: Object calulation way too long!!!

ryanhicks — Mon, 29 Jul 2013 18:13:10 GMT

so you are loading all of the data instead of loading only the 5 years you need?

if htis is the case i think what you shoudl do is do the pull form the warehouse and then reload your resicdent table with the where condition.

You were told to pull all the data as the where condition slows down the SQL pull, and it can be done in qlik with a qvd or on a resident table alot faster.

But you should limit the data you have in your app. if you have twice the amount of data that you need, it will always be slow.

While the set analysis statement will speed things up, the abundance of data is a data modeling issue that should be corrected for the benifit of the applicaiton and the operating environment.

If i misunderstood your post, i appologize.

Re: Object calulation way too long!!!

sibrulotte — Mon, 29 Jul 2013 18:31:23 GMT

I already have limitations over my expressions like you described above. But I'm still calculating over 15 years of data, and the progress bar is still going like a turtle.

Re: Object calulation way too long!!!

sibrulotte — Mon, 29 Jul 2013 18:35:25 GMT

That is exactly what we are doing, building a qvd with 15 years, and loading the qvd into a qvw. I was trying to save the 15 seconds it took to do the load with the "with" clause by loading all data into the qvw, but it realy seems like it's a bad idea, I get monstruous calculations (I'm playing with 9 M rows instead of 3 M, which is already long)

Please tell me about the set analysis though, I have twiddled with that yet.

Simon

Re: Object calulation way too long!!!

ryanhicks — Mon, 29 Jul 2013 19:33:32 GMT

Set analysis is what JPapador was suggesting above. it is usefull when you want to look at a subset of data or comparison calulations/ratios. there is quite a bit of documetation out there for it.

here is a set analysis white paper that I use very often. it does a good gob of explaining set analysis and gives some good examples. White Paper

Re: Object calulation way too long!!!

sibrulotte — Tue, 30 Jul 2013 12:45:50 GMT

Ok, that's actually a very helpfull tool to use.

But it still takes a very long time to calculate, so I'm guessing it sifts through the 15 years to determine which ones are that have gldetail.fiscalyr = to my variable.

It takes the same amount of time as

=sum(if(GLDetail.Annee_fiscale=AnnCour AND KEY_Type_Transaction_ID<>'EB',GLDetail.Montant))

And I'm actually not getting a result from using :

Sum({$<GLDetail.Annee_fiscale={'AnnCourBilan'}>}GLdetail.Montant)

Maybe I got it wrong (besides not restraining the transaction type) ?