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

Evaluate Chart Expression at line level - Part2

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

Hello guys and thank you again for all your help!

As for the solutionstalwar1 presented, it work perfectly until we had to move up one "notch".

On the January problem, we had one table with a one-to-one relationship between productcode and market.

Now we have grouped Products and for each ProductGroup it's possible to have more than one Product and More than one Market.

The expected result shoul be somthing like this:

Like in January, the main objective is to to have a table where, for the stores that have soled each ProdCode we will have the Sum(SalesUnits) for the Sub-Markets (Categories) where this ProductCode belongs to.

Once again I thank you for your help!

Pedro Freire

Message was edited by: Pedro Freire

Re: Evaluate Chart Expression at line level - Part2

Anil_Babu_Samineni — Mon, 20 Mar 2017 04:55:25 GMT

I do check your FACT excel. There showing only this, What was the logic behind on it? Can you check your excel whether the values are corrupted or same

Prod_BMV	10000963
Prod_DUCATE	22425
Prod_ONDA	20631

Re: Evaluate Chart Expression at line level - Part2

pedro_freire — Mon, 20 Mar 2017 09:24:07 GMT

Hello Anil,

The FACT Excel has 2 sheets:

The FACTS sheet has the Data that is loaded into the example3.qvw.

The Expected Results sheet has some Pivot Tables with the expected result for each Product (BMV, DUCATE, ONDA).

The Logic behind the calculation for BMV, for example, is as follows:

For the Stores that sold (SContrib=1) bike BMV (Cascais, Ericeira, Espinho, Lisboa e Nazaré);
do Sum (SalesUnits) of the Sub-Market where BMV belongs (Trail, Café-Racer)

Hope this helps.

Thank you

Re: Evaluate Chart Expression at line level - Part2

sunny_talwar — Mon, 20 Mar 2017 11:10:02 GMT

I started looking at this yesterday and it seems fairly complex.... but having said that, I am still looking at this and will revert back if I find something.

Re: Evaluate Chart Expression at line level - Part2

pedro_freire — Mon, 20 Mar 2017 11:17:34 GMT

Ok. Thanks Sunny.

Pedro

Re: Evaluate Chart Expression at line level - Part2

sunny_talwar — Mon, 20 Mar 2017 18:09:13 GMT

May be there is a better way to do this and this might not be 100% correct.... but look at the attached

Prod_DUCATE isn't matching, but the Excel did not select all the Stores?

Lisboa and Nazare were not included, is there a reason why they were not included for Prod_DUCATE? Once I include them, the number changes to 10043269 which matches with the output I am getting above.

Re: Evaluate Chart Expression at line level - Part2

pedro_freire — Mon, 20 Mar 2017 18:37:41 GMT

The reason is that Lisboa and Nazaré haven't sold Prod_DUCATE (any of its Packs) and they shouldn't be considered for its calculation.

Check SalesUnits for Prod_DUCATE in this stores =0.

The SContrib qualifier sets in Fact-table if the product was soled or not.

Nevertheless, you are allmost there.

Re: Evaluate Chart Expression at line level - Part2

sunny_talwar — Mon, 20 Mar 2017 18:52:16 GMT

Check now....

Re: Evaluate Chart Expression at line level - Part2

pedro_freire — Mon, 20 Mar 2017 19:15:24 GMT

I think you got it!

I'll see if this synthetic table will not impact on dashboard performance, as it's another big issue on the original app,

but as far as the model goes, it works fine!

Thank you once again Sunny!

Re: Evaluate Chart Expression at line level - Part2

sunny_talwar — Mon, 20 Mar 2017 19:23:16 GMT

Avoiding the synthetic key in the attached example.... but will take slightly longer to run....