Calculating an average only for fields where you have full data

[#1]

Hi

I want to make a line chart which follows follows an average from one month to the next but some months I don't have data in every row so i have a table like this.

Sales

Month	Country	Ian	Bruce
Jan	UK	100	200
Jan	France	200	300
Jan	Spain	400	300
Feb	UK	100	N/A
Feb	France	300	275
Feb	Spain	400	300

So in this case I want my graph to show the average sales per country Ian and Bruce made each month but as I don't know what Bruce did in UK in Feb I want to ignore UK all together and just show the average of France and Spain as otherwise Bruce would look better than Ian on average in Feb despite selling less in all comparable countries. Similairly I dont want to include the UK in Jan as that would make it look like Bruces average went up when, on comparable countries, it went down. Hopefully that is reasonably clear. I think this should be doable with a set function or maybe clever usage of aggr but I can't see how to do it.

I know ignoring entries on such a small database could soon wipe out all my data but the actual database has thousands of entries each month so it shouldn't be a problem.

Thanks for any help,

Richard

[Michael_Tarallo]

Hello Richard,

I noticed that you posted this in the QlikView Expressor Community. I would like to clarify if your intention was to use the QlikView Expressor (QVE) Desktop product to perform this task - which can create the desired flow and create / load a QlikView .QVX data file that you would use in QlikView - OR were you intending on performing this with native QlikView Desktop and using QlikView scripting?

Please advise if your intention is to use QVE (QlikView Expressor)  - if so I will gladly work on a small sample for you.

If not - I will move this discussion to another forum.

Regards,

Mike T

[#3]

Hi Michael

I realised I had posted my question in the wrong place and already moved it to the Development (Qlikview Desktop) Community. That seemed to be a more appropriate location.

[Michael_Tarallo]

Richard - no worries.

However - I did create the sample output (.qvx file) using QlikView Expressor which can generate the QVX data file - I attached the .QVW and the .QVX file - let me know if this is what you were expecting.

You can see in the Dataflow below - that I removed all records where sales were missing and captured the appropriate country value for those records. Then I join that with the incoming records to that country that has been removed and perform an outer join to get me only the countries that have data - also eliminating the rest of the UK as you mentioned.

This does require an understanding of QlikView Expressor which is a new product that facilitates the creation of metadata-driven QlikView applications. If you are interested in learning more please visit: http://www.qlik.com/us/explore/products/expressor for more information.

Let me know if you are interested and I will attach the QVE project and data file I used to create this.

Dataflow

Regards,

Mike T

[#5]

Thanks for the help Michael, sadly I don't have Qlikview Expressor and what you've done looks quite different to Qlikview Desktop so I'm not sure how I could replicate this on there. Is that possible?

Also when I tried to open the .qvw file on my computer a window opened saying as I am using personal edition you can't open files made on another machine unless I use one of my four recovers but then I won't be able to open any files I have already made. Is there a way round this?

[Michael_Tarallo]

Hello Richard -

Understandable - I will make sure one of QlikView community moderators is aware of your post so they help you with a solution using native QlikView as well.

In regards to the message dialog box that pops up, that is default behavior when using the Personal Edition of QlikView. The only way around this that I am aware of is by purchasing a desktop license.

Regards,

Mike T

[swuehl]

You can try like this:

Transform your data in a straight table in script (creating a SalesRep field from your multiple SalesRep columns):

CROSSTABLE (SalesRep, Sales,2) LOAD Month,

     Country,

     Ian,

     Bruce

FROM

[http://community.qlik.com/thread/60513?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

Now you have a table with fields Month, Country, SalesRep, Sales.

Then create a chart (e.g. a bar chart) in the frontend and use Month and SalesRep as dimension (up to here, this is similar to the data layout Michael used).

Then use this as expression:

=avg({<Country -= {"=numericcount(Sales)<count(Country)"}>}Sales)

The syntax checker will highlight this with a red underline, but this is a bug in the syntax checker (not recognizing the -= (minus equal) correctly).

The set expression will filter Countries where you don't have a Sales entry for each Month.

You can probably also filter your data in the script (almost rebuilding the expressor functionality), but I think you asked for a set expression, so I worked on that (which also leaves your data itself untouched, so you could still check the Sales for UK, if you want.

Regards,

Stefan

edit:

If you want to remove the Country while loading, you can do it like:

INPUT:

CROSSTABLE (SalesRep, Sales,2) LOAD Month,

     Country,

     Ian,

     Bruce

FROM

[http://community.qlik.com/thread/60513?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

LOAD distinct Country as Country2Rem Resident INPUT where not isnum(Sales);

RESULT:

NOCONCATENATE LOAD * Resident INPUT where not exists(Country2Rem ,Country);

drop table INPUT;

[whiteline · Accepted solution]

You can do it like this.

[#9]

Thanks whiteline,

There is one slightly odd thing when using this method. If I now make a listbox for country and select France or Spain the graph changes and just shows me Avg(Bruce) and Avg(Ian) for all 3 countries but if I select UK then it shows me exactly the same graph as in your example even though UK data isn't even used in that graph.

Is there any way for the graph to work as before but when I select France in a listbox it just shows me the data for France?

Thanks