topic Re: Tracking distribution changes in a dimension in QlikView

Tracking distribution changes in a dimension

Sat, 10 May 2014 11:20:37 GMT

Hello All,

I work for an insurance company and I'm looking to do some work which will track changes in the distribution of our a business. So for example are we writing more business in certain areas compared to last month, are we writing more business with women etc.

Here is some sample data,

City	% of Total 2012 Sales	% of Total 2013 Sales	% Change in Distribution
London	50%	40%	-20%
Paris	30%	30%	0%
Rome	20%	30%	50%

I could put an alerter on the Final column to go Red when its above 30%, or something like that. This is all fine and I can make this.

For my actual data, I would want to create a cyclic group where "City" currently is, to show 50+ different dimensions, or possibly 50 different tables with a different dimension in each.

My question is this. How do I effectively alert the user to where one of the distributions has changed by a significant amount, without getting them to look at every table, or cycle through every table. If I have 100 tables it isn't feasible to expect the user to look at them all. So I want something that would indicate that certain dimensions are important to look at before the user has actually looked through the tables, so they don't need to search themselves.

I want an end result similar to this,

Important Distribution Changes
Age, Gender, City

This would point the user to look at the Age, Gender and City tables.

Any ideas?

Thanks

Re: Tracking distribution changes in a dimension

Sat, 10 May 2014 13:30:09 GMT

Hi Connor,

using aggr() you may do calculation of some expression for some dimension.

Next you may aggregate it with for example max or min to show or to check some condition, etc.

This is useful if you want to do it regarding user selection (look for example). But if you have big data volumes it may be slow.

You may also calculate it during reload, but it will be independend from user selections then.

regards

Darek

Re: Tracking distribution changes in a dimension

Sat, 10 May 2014 13:59:56 GMT

Hi Darek,

Thanks for the Reply! I only have personal edition at home so cant access your file, sorry . I have used the Aggr() function a lot and consider it to be incredibly useful, but i don't see how it helps me here. Can you give me the example of the formula you used?

I get that I can use Aggr() in my formulas, but how can I alert people to specific dimensions where the distribution has changed?

Thanks for any help you can provide,

Connor

Re: Tracking distribution changes in a dimension

Sat, 10 May 2014 15:35:05 GMT

Connor,

you told taht you know condition to show some value in red color when you calculate this KPI in chart with some dimension.

I understand, that if any point in your dimension is red, you would like to show that dimension name.

You may do this using expression like this:

if(sum(aggr( if( [your red condition],1),City))>0,'City')&','&if(sum(aggr( if( [your red condition],1),Gender))>0,'Gender'),.....

regards

Darek

Re: Tracking distribution changes in a dimension

Sat, 10 May 2014 17:31:13 GMT

Hi Darek,

That works a treat!! Just put the expression in a text box and it works great. Going to be annoying to make a new expression for each aggregation but oh well.

Thanks Darek!

Re: Tracking distribution changes in a dimension

Sat, 10 May 2014 17:35:31 GMT

You can prepare expression in kiss script by concatenation

Regards

Darek

10-05-2014 19:31 użytkownik "Connor Sugden" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Tracking distribution
changes in a dimension
reply from Connor Sugden<http://community.qlik.com/people/Connor.Sugden92?et=watches.email.thread>in *App
Development* - View the full discussion<http://community.qlik.com/message/525909?et=watches.email.thread#525909>