topic Re: Filtering journal entries for above average values in QlikView

Filtering journal entries for above average values

Tue, 14 Apr 2015 09:12:28 GMT

Hi,

I am new to qlikview and would like to try a small analysis on data from a ledger (basically fields like (monetary) value, entry date, booking date, account, user etc.) for evaluation purposes. What i did so far is loading the data and visualizing journal entries by entry, by week day, hour and user.

Now I would like to filter all views to show only entries with above average values. My first idea was to do this in the ETL script by inline loading a dimensional table "outliers" with values A and B and adding a field If(VALUE> 25000, 'A', 'B') as OUTLIERS. This works and I can now filter by using a multi box.

Is it possible to replace the fix value '25000' in the ETL script by an expression that actually calculates the correct average over all values (or better, average + standard deviation)?

Alternatively, would it be possible to do the filtering dynamically in the chart? We are talking about 1-2 mio. entries, so this might get a bit slow. Still, I would like to give it a try to see how qlikview handles this.

Thanks for your input!

Ralf

Re: Filtering journal entries for above average values

Tue, 14 Apr 2015 13:56:38 GMT

Hi,

maybe I found one solution myself for filtering the journal in a chart. I added the following calculated dimension:

if(VALUE > AVG(all total VALUE), 'A', 'B')

where A entries are outliers and B entries are average. Now, I can filter the outliers by clicking on any A entry in the chart.

Does anyone have an idea for a solution in the load script?

Cheers

Ralf

Re: Filtering journal entries for above average values

ramoncova06 — Tue, 14 Apr 2015 15:25:08 GMT

you could do this with a preceding load

having clause in Qlikview

Re: Filtering journal entries for above average values

Wed, 15 Apr 2015 08:03:26 GMT

Thanks for the idea, I am going to give that a try. I guess it will take two separate loads then, if I want to load the entire data and add a category field (one load for values above average and one for the rest).

Cheers

Ralf