topic Re: Evaluate Chart Expression at line level - Part3 in QlikView

Evaluate Chart Expression at line level - Part3

pedro_freire — Wed, 25 Nov 2020 16:16:04 GMT

Hello again,

I'm building upon an initial solution bystalwar1‌ that worked great untill now, but as business request changed, I'm having trouble changing the model to include this new requests.

The question I'm trying to answer, using a Chart (Straight or Pivot Table) layout, is the following:

1. Considering that we have a PRODUCT table where:

one Brand has more than one Product
one Product has more than one Pack,
each Pack can be in more than one Sub-Markets (or Categories)

2. Considering that we have one FACT table where our measure is SALES_UNITS and our dimension are Pack, Store and a SALES_CONTRIBUTION (being a binary qualifying if the line is relevant for SALES calculations or not).

We want to know for each Product (or Brand, or Pack) on the Pivot table, what is the total Sales Units on the Product (dimension) respective Sub-Markets, but only on the stores that have sold (that have SALES_CONTRIBUTION =1) the product.

Something like this:

Product (dimension)	Sales Units (expression)
Prod_A	Total Sales_Units of the Prod_A's Sub-Markets, on the Stores that sold Prod_A
Prod_B	Total Sales_Units of the Prod_B's Sub-Markets, on the Stores that sold Prod_B
Prod_C	…

The general idea is to change dimensions dynamically on user selection, so the data model has to be flexible and dynamic, and preferably to use the same expression for all dimension levels.

Please see attached problem model.

Thank for your help.

Best Regards

Pedro Freire

Re: Evaluate Chart Expression at line level - Part3

sunny_talwar — Thu, 23 Mar 2017 18:41:38 GMT

And I thought we were done

Re: Evaluate Chart Expression at line level - Part3

pedro_freire — Thu, 23 Mar 2017 20:23:34 GMT

With the model I published it worked fine.

However the solution mandates more flexibility on the dashboard side, so the problem was on the model I published, that allowed solutions on the script side.

If you remeber the first solution had something like this on the pivot line level:

=Sum(Aggr(Sum(TOTAL <Store, Market> SalesUnits), Store, ProdName, Market))

My solution must be more arround aggregations, with less work on the script side.

Re: Evaluate Chart Expression at line level - Part3

sunny_talwar — Fri, 24 Mar 2017 09:11:24 GMT

Hey Pedro... I will be working on this today...

Re: Evaluate Chart Expression at line level - Part3

pedro_freire — Fri, 24 Mar 2017 10:05:11 GMT

Great!

I will be online, please let me know if I can be of any help.

Re: Evaluate Chart Expression at line level - Part3

pedro_freire — Fri, 24 Mar 2017 13:37:35 GMT

The bookmarks on the 1st example where not showing the correct result.

For each ProductName or Brand they where showing all stores, and not only those who have sold the item

The file attached is the same QVW with the correct bookmarks.

Thank You

Re: Evaluate Chart Expression at line level - Part3

sunny_talwar — Fri, 24 Mar 2017 15:33:40 GMT

Don't think I know how to solve this without script intervention.... There is only one guy I can think of who might be able to resolve it -> swuehl‌. If he has time, I am sure he will respond back... else I am not sure what to tell you.

Re: Evaluate Chart Expression at line level - Part3

pedro_freire — Mon, 27 Mar 2017 08:15:41 GMT

Thank you Sunny for your time I'll try swuehl‌, he has helped me in other situation in the past.

Re: Evaluate Chart Expression at line level - Part3

marcus_sommer — Thu, 30 Mar 2017 14:37:31 GMT

I must admit I didn't understand the problem - what's wrong or missing?

- Marcus

Re: Evaluate Chart Expression at line level - Part3

pedro_freire — Fri, 31 Mar 2017 09:19:02 GMT

Hello Marcus,

Thank's for your attention!

The main question is:

What are the total Sum (SalesUnits) on the possible Sub-Markets only on the stores that sold the product.

Notes:

Each Product can have more than one pack and each pack can be in more than one Sub-Market;
The FACT table granularity is at pack level;
Possible Sub-Markets are the ones the product belongs to;
I've to present the information in a pivot chart, with product as a dimension, and with no selections made;
This is a small model of the problem and the solution has to be integrated in a larger scale app so, the heavy work should be in the expression side, with small or no work on the script side.

So far we came up with something like this:

Sum(

Aggr(Sum(TOTAL <STOREID,[Sub-Market]> {<SALES_CONTRIBUTION={1}>} SALES_UNITS),[Sub-Market],STOREID,[ProductName]))

The problem is, when one product as more thas one pack, and each pack has a different Sub-Market, the expression only returns Sum(SalesUnits) of the Sub-Markets that have product's packs with sales, i.e.

if for some instance one of the pack of one product has no sale in one store, it will not sum the correspondent pack's Sub-Market, despite that the product belonging to that Sub-Market and it has sales of other packs.

in short, if i have a one-to-one relation between product and Sub-Market, the expression returns the correct result. If one product is in more than one Sub-Market I start to have problems...

Hope this wasn't a to messed up answer!

Best regards

Pedro Freire

Re: Evaluate Chart Expression at line level - Part3

sunny_talwar — Tue, 04 Apr 2017 13:25:35 GMT

Any update marcus_sommer‌. Do you think it is possible to do what Pedro is looking for here?

Re: Evaluate Chart Expression at line level - Part3

marcus_sommer — Wed, 05 Apr 2017 11:09:27 GMT

I'm not sure if I have really understood the problem. Therefore I hope my small adjustment is helpful in any way.

- Marcus