topic Re: Is it possible at all to get a summary table? in QlikView

Is it possible at all to get a summary table?

nenadvukovic — Fri, 01 Aug 2014 10:04:16 GMT

Hi, I need to move this Excel solution into QV and make it dynamic. I got details but do not know how to get the summary table if at all possible. This is part of a general sales analysis QV app.

Issues:

1. Get the last cumulative % for each class (marked with black arrows)

2. Get the count of items by class (marked in orange circle)

If the initial idea is not doable to get these totals by class in the details table can also be solution.

Any idea is welcome. Thanks in advance

Re: Is it possible at all to get a summary table?

marcus_sommer — Fri, 01 Aug 2014 12:23:18 GMT

You need a dimension for this which contained the logic from the colors. Then you could use these dimension in a table-chart and the expression will be depend from your raw-data.

- Marcus

Re: Is it possible at all to get a summary table?

rustyfishbones — Fri, 01 Aug 2014 12:28:33 GMT

Does your Data model contain the Product Classes?

Re: Is it possible at all to get a summary table?

rustyfishbones — Fri, 01 Aug 2014 12:39:20 GMT

Do you mean something like the attached

Re: Is it possible at all to get a summary table?

sujeetsingh — Fri, 01 Aug 2014 12:44:14 GMT

Of course man visit different demo dashboard at Qlik website.

It is possible to design and develop summary tab.

Just you need to design your data model as per the same .

Re: Is it possible at all to get a summary table?

nenadvukovic — Fri, 01 Aug 2014 17:00:09 GMT

Thx. Just a small clarification the picture in my initial question is from QV and not Excel.

I do not have Class stored in my data model as this depends on the selection user makes. A user can select different days for sold in last N days, different product groups, customers, etc.

@Alan: This would work if I have the Class in the data model, but I have it only in the pivot table and it is dynamic as described earlier.

Re: Is it possible at all to get a summary table?

nenadvukovic — Fri, 01 Aug 2014 18:24:20 GMT

You missed the point here. If interested please take a look into my last reply. Thx

Re: Is it possible at all to get a summary table?

rustyfishbones — Fri, 01 Aug 2014 20:00:02 GMT

You could create the Class in the Script with a Nested IF statement based on the Product Codes

Re: Is it possible at all to get a summary table?

rustyfishbones — Fri, 01 Aug 2014 20:06:57 GMT

Something like this for the Nested IF

IF(Code = 0146, 'CLASSA',

IF(Code = 0142, 'CLASSB',

IF(Code = 0141, 'CLASSB'))) as ClassTest

Re: Is it possible at all to get a summary table?

nenadvukovic — Fri, 01 Aug 2014 20:41:20 GMT

Sorry Alan, but the Class gets calculated dynamically with tens of selections before and then by calculating qty sold in x number of days where x can be anything. This is impossible to calculate in the script

Thanks anyway

Re: Is it possible at all to get a summary table?

marcus_sommer — Sat, 02 Aug 2014 08:44:11 GMT

If you want use a chart for this instead of several textboxes you will need a dimension but thes could you dynamically create within the gui. There are functions like valuelist(), valueloop(), concat(), getfieldselections() ... most wrapped in a aggr() which you could use to build such a dynamically dimension.

- Marcus

Re: Is it possible at all to get a summary table?

nenadvukovic — Sat, 02 Aug 2014 09:21:58 GMT

Thx Marcus. I'll check the functions you listed. Please note that those are text objects placed into a container. Does this change the approach?

Re: Is it possible at all to get a summary table?

marcus_sommer — Sat, 02 Aug 2014 15:34:25 GMT

A container doesn't change the approach - it is generally a clever possibility to collect several objects, not only textboxes and you could also use nested container in other containers.

If you have only a few KPI's which are always the same then is the effort to create the textboxes manageable and you have the advantage to use more different formatings - if not a chart is more favourable.

- Marcus

Re: Is it possible at all to get a summary table?

nenadvukovic — Sun, 03 Aug 2014 08:11:27 GMT

Here you can find this questionable part extracted from a multi-sheet, multi-object qvw with only one of hundreds of possible data.

If anyone can populate text objects with two different set of values for each Class I would be very much grateful. Thx

Re: Is it possible at all to get a summary table?

Oleg_Troyansky — Sun, 03 Aug 2014 16:55:25 GMT

Nenad,

your problem is way too much complicated for anyone to try and untangle it, in the boundaries of a community discussion.

In general, what you are trying to can be done with a nested AGGR - first, at the SKU level, and then, at the Class level, and I'd recommend to simplify the initial premise by a lot before trying to formulate the AGGR fucntions.

cheers,

Oleg Troyansky

www.masterssummit.com

Re: Is it possible at all to get a summary table?

nenadvukovic — Sun, 03 Aug 2014 19:27:54 GMT

Hi Oleg,

It's good to hear from you. I've been trying to sort this out in last 3 days, including weekend.

This is all about getting both the details (which I got) and the summary of the "Glenday Sieve" analysis.

I can't use the aggr() function as I need to use a calculated dimension.

I'll have to tell users to continue using Excel but this time they can get full details from QV and then manually fill in only the summary table.

With all being said I think the answer to my initial question is "no, it's not possible in this case".

Cheers

Here is the complete part related to this based on one selection out of n (the Class column is not needed and it present as this is still in development). In addition there is also the option for users to select/enter a different days of sale:

Re: Is it possible at all to get a summary table?

marcus_sommer — Mon, 04 Aug 2014 07:25:56 GMT

Maybe it is easier for you to do the calculation in excel but I'm quite sure that it could be calculated in qv, too. Especially if you could calculate single-parts in textboxes will it be possible to do the same in a table-chart. I see no reason why aggr() shouldn't work - the aggr() function will be most often used for dynamically dimensions - you will find many examples here in the community.

- Marcus

Re: Re: Is it possible at all to get a summary table?

nenadvukovic — Mon, 04 Aug 2014 11:41:30 GMT

This is something I'll definitely not let go as it is against my nature. Here you can find more details. Note this is just a small part of one general sales dashboard and analyses app.

I want to get these parts on the individual text fields but do not have all elements stored in the QV data model. I have only base data needed to perform various calculations over dynamically selected data as you can see in the last screenshot provided.

Re Aggr() & Calculated Dimensions: I've found this in the QV help file for the Aggr() function: "... Each dimension must be a single field. It cannot be an expression (calculated dimension). ... "

Why I use a calculated dimension? You can end up with some rows where all 3 fields, which btw provide different quantity sources (qty sold in n days, current balance and safety stock), can be 0 I need to eliminate them to only show a meaningless data and at the same not to distort the calculations. Here is the actual one labeled SKU:

=if(aggr(sum(GlendayBalance), [Stock Item]) > 0

or aggr(sum(GlendaySafetyStock), [Stock Item]) > 0

or aggr(sum({<Date={'>=$(=date(today() - $(vLastXDays)))'}>} if(Qty < 0, 0, Qty)), [Stock Item]) > 0, [Stock Item])

Why I put this part? "... if(Qty < 0, 0, Qty) ..." Because there is a request that negative quantities of sold items (credit notes) should be shown as 0.

I've modified the test app so you can try get it.

Thanks to all for looking into this.

P.S. And there is a similar problem which is also not answered. You can find it under

count elements by pareto class

Re: Re: Re: Is it possible at all to get a summary table?

nenadvukovic — Mon, 04 Aug 2014 13:04:48 GMT

As expected in both cases I would need to find a way to have the Class value stored into the QV data model (Class1) which is, at least I think, impossible if I want to give users ability to play with selections which changes the Classification:

Getting the number of SKUs by Class into the text objects I can't use the expression ("Class Dynamic")
Getting the number of SKUs by Class in the detail table itself I also can't use the expression ("Class Dynamic"). This is why I wrote aggr() can be used only with dimensions and not calculated expressions.

Re: Re: Re: Is it possible at all to get a summary table?

nenadvukovic — Tue, 05 Aug 2014 14:13:14 GMT

I was inspired today and once more answered my own question .

The needed totals by Class are shown in the TOTAL line above 8 extra columns added to be able to get those max items and max percentages.This is the best I can get to satisfy all requests and retain it dynamic with no extra fields in the data model. Only columns marked in orange exist in the data model used in this table.

Thanks to everyone who tried to help me out as any idea is helpful.