topic Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension in QlikView

Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

Arthur_Fong — Sat, 16 Nov 2024 21:30:32 GMT

Hi all,

I have a problem with pivot table when expanding the month columns, it does not work.

This occurs when i attempt to apply a calculated dimension(toggle between Month and Quarter).

The example below is what I would want to achieve:

Month selected is Jun, but Month for May and Apr is still expandable.

This works on normal Month Dimension, when i apply the calculated dimension, the problem occurs where:

Month selected is Jun, May and Apr could not be expanded.

Any idea on how to achieve the expected result?

Attached is a sample qvw with the table on 'Month\Quarter' sheet.

Thanks and regards,

Arthur Fong

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

uacg0009 — Wed, 30 Jan 2019 03:43:56 GMT

Hi,

I saw your qvw, I think the problem is not the calculated dimension,

because you use {<Month=>} set analysis in expression,

so when you choose Q2 and Jun, the pivot table shows May, Jun and Jul.

If you delete the set analysis, it will be fine, please try.

Aiolos Zhao

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

Arthur_Fong — Wed, 30 Jan 2019 03:48:17 GMT

if i remove set analysis, i would get only Jun.
Thanks.

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

adamgaox — Wed, 30 Jan 2019 05:07:00 GMT

Hi JKfong,

I would suggest you to declare the requirements you want.

Best Regards.

Jiawen.

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

adamgaox — Wed, 30 Jan 2019 05:11:04 GMT

Hi Jkfong,

If you want to know your Jun sales value, just collapse the button on Jun why u need to select Jun in the selection?

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

Arthur_Fong — Wed, 30 Jan 2019 05:43:19 GMT

Hi Adam,

In the dashboard, when user click on Quarter, they are able to see Q1-Q4, YTD Actual, and PY Actual.

For YTD Actual, user shall select on Month to look at the YTD figure.

Eg: When user select Jun, he should see Jan-Jun figure(cumulative sum) on the YTD column.

That is the reason why Month have to be selected as well.

Thanks.

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

uacg0009 — Wed, 30 Jan 2019 05:50:31 GMT

Hi,

Sorry, just now I misunderstood what you want.

How about if you check the "Always Fully Expanded" in Presentation tab.

Aiolos Zhao

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

Arthur_Fong — Wed, 30 Jan 2019 05:54:04 GMT

Hi Zhao,
This option will not allow the user to collapse when they want to.
Thanks.

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

adamgaox — Wed, 30 Jan 2019 07:13:33 GMT

Hi Jkfong,

I understand when u select month it will change the YTD calcuated period but how it matters the collapse and expand functions in the pivot table.

From my point of view they're exactly different requirements.

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

Arthur_Fong — Wed, 30 Jan 2019 10:07:05 GMT

Hi Adam,
They are different requirements but how to put these requirements together?
Below is the flow of the scenario:
Eg: 1. User selects Juneto see Jan to June YTD figure.
2. They saw a big figure on May and would want to expand and see where the figure comes
from.
3. They could not expand May as shown in the screenshot above.

Thanks.

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

adamgaox — Thu, 31 Jan 2019 04:19:28 GMT

Hi Jkfong,

I would suggest you implement bar chart by Drill Down group to display YTD sales value or any other KPIs.

When user notice any obvious growth or decrease they can just pick up the Month in the view to drill down to product to see where the increase coming from:

Re: Pivot Table Month Column Expansion Not Working When Using Calculated Dimension

Arthur_Fong — Thu, 31 Jan 2019 04:36:45 GMT

Hi Adam,
This report is implemented under Financial Reporting.
User would prefer to have table reports rather than graphical charts.

Thanks.