topic Re: Display values on a scatter chart without aggregating and hard-coded filter in QlikView

Display values on a scatter chart without aggregating and hard-coded filter

Wed, 04 Jul 2012 15:48:37 GMT

Doubt 1 :In the attached qvw, I created a qvw. Here I want to display the dots at each record, that is one dot for one record. I have used the dimension as bmc_desc, so

the final dots are aggregated at the bmc level. I have used expressions as sum(incremental_sales) and sum(imcremental profit), but I want to show the

incremental sales and incremental_profit of each dot. But nothing is displayed when I use just "incremental_sales" as the expression.

Please suggest how to display dots at each record level and then all records belonging to a single bmc have one color.

data_context

sub_category_code

sub_category_desc

family_code

week_id

week_num

promo_sales

baseline_sales

incremental_sales

incremental_profit

discount

avg_nbr_of_baskets

baseline_nbr_of_baskets

basket_size_impact

incremental_traffic

net_basket_impact

quadrant

weekly_amt

bmc_desc

avg_basket_size

baseline_basket_size

brand

vendor_name

promo_code

promo_start_date

promo_end_date

supplement

press

radio

smart_shopper

instore


																												0
-Super-All	G120201	RICE	11852	201146	46	18788.79	16913.97	-468.747	-2595.42	0.243324	301	223.8628	-155.87	77.13719	12562.26	4	8079.1	KIDS CEREALS	615.2133	771.083637	KELLOGG'S	Kellogg Company SA (Pty) Ltd	NULL	00:00.0	00:00.0	1	0	0
I-Super-All	G120201	RICE	11852	201147	47	15233.55	15495.99	-2162.55	-3633.04	0.243466	246.6667	210.244	-84.941	36.42267	5856.349	4	6550.45	KIDS CEREALS	651.0967	736.037626	KELLOGG'S	Kellogg Company SA (Pty) Ltd	NULL	00:00.0	00:00.0	1	0	0

Doubt 2 : Is there a way to hard-code the filter. For example in the qvw attached, you can see a filter DAta context which has three values. Is there a way to remove that list box and by default set the filter condition to All

Re: Display values on a scatter chart without aggregating and hard-coded filter

Wed, 04 Jul 2012 17:49:05 GMT

May be this would help. Check out the expression background and set analysis for your questions respectively.

Regards,

Kiran Rokkam.

Included attachment

Re: Display values on a scatter chart without aggregating and hard-coded filter

Wed, 04 Jul 2012 18:27:17 GMT

HI Kiran,

Thanks fr the reply.

CAn you please explain the process?

Why did you add so many dimensions? Can you please take me through it

Re: Display values on a scatter chart without aggregating and hard-coded filter

Wed, 04 Jul 2012 18:29:39 GMT

Also, can you help me on the second question(doubt)??

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 00:48:50 GMT

If you expand the expressions you can see background color options. You can use them to specify the color of the bubbles.

Regards,

Kiran Rokkam.

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 00:49:49 GMT

I added these dimensions as you wanted a bubble for each record. Keep only the ones you need (just to show how it works).

Regards,

Kiran Rokkam.

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 13:33:37 GMT

Hi Kiran,

All-All	G120201	COOKING OILS	11852	201146	46	5881.71	5507.957	-359.885	-1355.820286	0.000214	83.66667	75.79664	-17.3506	7.870029	4022.524	4	4603.746	KIDS CEREALS	678.2233	695.573884	KELLOGG'S	Kellogg Company SA (Pty) Ltd	NULL	00:00.0	00:00.0	1
All-All	G120201	COOKING OILS	11852	201147	47	5135.24	5313.199	-818.488	-2043.165873	0.000125	70.33333	76.98693	-29.1778	-6.653596	-6619.85	4	4019.443	KIDS CEREALS	657.32	686.497823	KELLOGG'S	Kellogg Company SA (Pty) Ltd	NULL	00:00.0	00:00.0	1
All-All	G120201	COOKING OILS	11852	201148	48	6221.06	7750.665	-2305.57	-1550.718239	0.000108	89	117.8203	-96.1857	-28.820294	-38086.7	4	4869.376	KIDS CEREALS	928.3067	1024.49234	KELLOGG'S	Kellogg Company SA (Pty) Ltd	NULL	00:00.0	00:00.0	1

Here in this qvw, in the expression I gave sum({<data_context={"All-All"}>}incremental_sales)

So that it will select only All-All records and it indeed does select those records for which datacontext = All-All. But when you click cooking oils, you can see two dots : family and kids cereals. I went to the data source and found two bmc_desc : kids cereals and family. The above are the three records I found for kids cereals, but on the chart I am able to plot only one dot. As per the requirement I want three dots one for each of the above.

I think it is showing only one dot because we are using sum in the expression and hence it is aggregating the results. Is there a way to show all the records. Also, what is the exact purpose of dimensions?

thanks!

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 16:01:34 GMT

Uday,

The no. of dots depends on the no. of distict values combinations of the dimension. For the selections made the dimensions (bmc_desc, sub_category_code and sub_category_desc) has only two distinct combinations (you can check that by adding these three fields into a table box). Add necessary dimensions to increase the dots. ( Personally i dont recommed this).

Regards,

Kiran Rokkam.

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 16:05:03 GMT

So in a way, if we give the unique of tat table as dimensions then we ll get the unique rows.

Am I right?

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 16:10:20 GMT

Yes. So clarify the business requirements and start with QVW. One more recommendation is alias fields name so that users can understand them. Keep only the necessary fields and remove the rest (its saves lot of time) as its easy to add fields anytime.

Kiran.

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 16:12:55 GMT

thanks for the reply.

Yeah I will follow that.

So, here in the expression why can't I just use incremental_sales? Should I always use sum(sales) ?

I tried with just sales but did not get output

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 16:17:22 GMT

Expression needs group functions. Check out this example.

Kiran

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 16:18:21 GMT

Adding to the doubts on dimensions : Does dimension have differnet meaning in scatter chart and bar chart?

As you said, combination of dimensions in scatter chart gives us the dots on the chart. How does dimension in the bar chart change things?

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 16:33:37 GMT

They mean the same in both the cases. No. of rows for any chart is the same as a straight table. Only the visualization is different.

Kiran.

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 17:12:44 GMT

Just to confirm. Hardcoding the filter condition :

I am using set analysis sum({<data-context="All-All'>} incremental_sales) to limit the search result by default to all-all. Am I correct?

doubt 2:Is there a way to restrict the values in the filter/list box? Do we need to add expressions there as well or is there any other wya?

Re: Display values on a scatter chart without aggregating and hard-coded filter

Thu, 05 Jul 2012 19:15:08 GMT

kiran,

When I give around 4 column names(the combination of which uniquely dtermines the row) the chart is showing dots at that level( I checked for only one value and that was right, I need to do testing on more values).

I do not know how to set the legend, I am not mentioning anywhere that bmc_desc has to be the legend or data_context has to be the legend.

I want the legend as bmc_desc but for some slections i see bmc)_desc as the legend and for some I see data_context as the legend. Is there a way to have just one fixed field as the legend?

thanks!

Re: Display values on a scatter chart without aggregating and hard-coded filter

Fri, 13 Jul 2012 14:17:37 GMT

data_context	sub_category_code	sub_category_desc	family_code	promo_code	week_id	promo_sales	baseline_sales	bmc_desc	incremental_sales
Inland-Hyper-All	G120201	CEREALS	1041	1	04	13.94	2.318	a	5.82
Inland-Hyper-All	G120201	CEREALS	1041	23	05	1.82	5.237	a	6.19
Inland-Hyper-All	G120201	CEREALS	10577	3	13	101.67	64.511	a	59.7
Inland-Hyper-All	G120201	CEREALS	10577	4	14	100.79	12.312	b	21.38
Inland-Hyper-All	G120201	CEREALS	10577	5	35	121.55	722.813	b	41.62

I am writing this again, to be clear with teh requirements . I want to represent every row as a dot in the scatter chart and all these shoud be grouped by bmc_desc. I.e there should be 5 dots as per the above example two belonging to B(one color) and 3 belong to A ( another color).

Attached is my qvw file. I mentioned bmc_desc, sub_cat_desc, data_context , and promo_code as the dimensions. (AS the combination is a unique in that table, so I chose these 4 dimensions to represent each row.

I m not sure how to set the legend, I want to see bmc_desc as the legend and under that a and b and the colors they represent, I could not find a place to set the legend properties.

Attached is the qvw

Re: Display values on a scatter chart without aggregating and hard-coded filter

MayilVahanan — Fri, 13 Jul 2012 14:49:04 GMT

HI,

If you want to display color based on the bmc_desc oly ?? If so, check the attached file. If you use dimension as in Group1 , you will use GetCurrentField(grupname) in window title..

If you used dimension as Groupt1, based on bmc_desc values, color appear in chart for each dimension in group..

Hope it helps..

Re: Display values on a scatter chart without aggregating and hard-coded filter

Fri, 13 Jul 2012 14:55:05 GMT

As I told you earlier, I use free edition. So, I can't see the solution.

But I think you told me this before, but the problem with that Group thing is that, When I select bmc_desc, all the records are aggregated at the bmc_desc level i.e for bmc_desc = a if there are 4 records ,, all the 4 records are summed up for that bmc..but I want to show all those 4 articles as 4 dots on the chart.

Thanks!

Re: Display values on a scatter chart without aggregating and hard-coded filter

MayilVahanan — Fri, 13 Jul 2012 15:01:35 GMT

ok . You want to see color based on bmc_desc values?

if so, used in expression background