topic Re: Challenge with Pivot Table Total in QlikView

Challenge with Pivot Table Total

Fri, 28 Aug 2015 19:28:03 GMT

Hi all,

probably I'm not so expert with Qlikview but I have a big issue in a pivot table to calculate a total when I filter for a dimension.

Following the example:

In this example the column 'Product CR' is calculated as :

[Qty Upsell]/SUM(TOTAL <MAIN_PRODUCT_UPSELL> {$<ACTION = {"I"}, MAIN_ORDER = {"1"}, WEB_CHANNEL = {"0"}>}QTY)

Final user would like to filter only one upsell product and see the correct 'Product CR'; with the previous formula if I select only one row of upsell , the formula calculates only the total quantity for that upsell product.

It would be appreciate any good solution.

Thanks,

Romina

Re: Challenge with Pivot Table Total

swuehl — Fri, 28 Aug 2015 19:36:52 GMT

Try

[Qty Upsell]/SUM(TOTAL <MAIN_PRODUCT_UPSELL> {1<ACTION = {"I"}, MAIN_ORDER = {"1"}, WEB_CHANNEL = {"0"}>}QTY)

using set identifier 1 or

[Qty Upsell]/SUM(TOTAL <MAIN_PRODUCT_UPSELL> {$<ACTION = {"I"}, MAIN_ORDER = {"1"}, WEB_CHANNEL = {"0"}, UPSELL_PRODUCT= >}QTY)

Re: Challenge with Pivot Table Total

Fri, 28 Aug 2015 21:27:43 GMT

Hi swuehi,

thank you so much.

Yes of course it works with the second option, but actaully I have another issue.

When I filter only one upsell product the 'Product CR' is correctly calculated but the other columns are filtered and the total is not correct.

So I'm trying to think how to fix the requirement because the objective of business is to filter only the main product or only the upsell or both products.

Do you think that is possible the solution: when I filter an upsell product I see all combinations of main product (related to the upsell product selected) and upsell products.

I wait for your idea,

Romina

Re: Challenge with Pivot Table Total

swuehl — Fri, 28 Aug 2015 21:36:26 GMT

How do the other expressions look like? Could you upload a small sample QVW that demonstrates what you are trying to achieve (could contain some sample mock up data)?

Re: Challenge with Pivot Table Total

Sat, 29 Aug 2015 10:19:42 GMT

Hi swuehl,

I don't know if it is possible to do that: when I look for an upsell product I can see in the pivot table all possible combination between main product (related to my filter upsell product) and upsell products. IN this way the user can see always the total performance of main product related to the upsell product that I have set. With the formulas that I have put the filter for upsell product doesn't work anymore.

Thanks a lot,

Romina

Re: Challenge with Pivot Table Total

swuehl — Sat, 29 Aug 2015 10:33:35 GMT

Not quite sure what you are trying to achieve, keep the upsell products unfiltered within the main product, but filter the main products displayed?

Something like attached?

Re: Challenge with Pivot Table Total

Sat, 29 Aug 2015 10:46:37 GMT

Greats!

What I want to achieve!

Last question: could you explain me better how the formula works?

SUM({<ACTION = {"I"}, MAIN_ORDER = {"1"}, WEB_CHANNEL = {"0"}, UPSELL_PRODUCT =, MAIN_PRODUCT_UPSELL = p() >}QTY)

Thanks a lot,

Romina

Re: Challenge with Pivot Table Total

swuehl — Sat, 29 Aug 2015 10:49:59 GMT

I just added

MAIN_PRODUCT_UPSELL = p()

to the set expression. The p() function will return the set of MAIN_PRODUCT_UPSELL values that are possible with your current selection. So if you select an UPSELL_PRODUCT, there will be an implicite selection on the related MAIN_PRODUCT (within that aggregation calculation). All other MAIN_PRODUCTs will be removed from calculation by your manual selection.

Re: Challenge with Pivot Table Total

Sun, 11 Oct 2015 18:04:32 GMT

Hi Swuehl,

after business test I discovered that in the set analysis I need to find the MAIN_PRODUCT_UPSELL values that are possible from my UPSELL_PRODUCT selection, excluding the possbile values that have not an UPSELL_ID related.

The UPSELL_ID is in the UPSELL_SET table as you can see in the previous example.

I have tried adding UPSELL_ID = {'*'} , UPSELL_ID -= {"=Null()"} but they doesn't work.

Thanks a lot,

Romina