Planning Analytics

Hi,

I am hoping the new cube viewer in PAW will address a current limitation which still necessities some PAX reports in our applications.
For our applications, it would be ideal if all our reports could be built in PAW, but the limitation outlined below is one reason why we still need to present some PAX sheets via PAW.

The limitation I speak of is as follows,

If I create a cube view, which allows the data to be filtered via MDX, I am not able to get a meaningful grand total for the view.
What I basically mean by this is a one line total row at the bottom of the view summing up all the rows above within the current view and this total dynamically changing as the view filters change.

The following image shows test data filtered by Vacant Employees and Position Leafs.
It returns 2 rows and what I would like to see is a third row which sums up the 2 rows for every column and when I change the filter it recalculates the total based on the new data returned to the view:


As can be seen in the image above I have no meaningful grand total that adds up the rows.

In PAX we can achieve this via dynamic reports which allows us to put Sum formulas at either the top or bottom of the report columns and this gives us grand totals.

Will the the new cube viewer allow for these grand totals or is there any plans to include this in a future update of PAW?

Personally, as things stand, I think it is a stretch to call PAW's calculation and summarising options powerful.
Adding in this feature would make that claim more plausible.

regards,

Mark

------------------------------
Mark Wragg
------------------------------
#PlanningAnalyticswithWatson

HI Mark,
Maybe I am missing something in your use case but would right-clicking the Row Header then Summarize all... not do what you need?
I know toggling to show total leading or trailing does not work on this but should give you a total line at the very least.

------------------------------
George Tonkin
------------------------------

Original Message:
Sent: Fri March 26, 2021 09:27 AM
From: Mark Wragg
Subject: PAW Summary Totals for data in the current view

Hi,

I am hoping the new cube viewer in PAW will address a current limitation which still necessities some PAX reports in our applications.
For our applications, it would be ideal if all our reports could be built in PAW, but the limitation outlined below is one reason why we still need to present some PAX sheets via PAW.

The limitation I speak of is as follows,

If I create a cube view, which allows the data to be filtered via MDX, I am not able to get a meaningful grand total for the view.
What I basically mean by this is a one line total row at the bottom of the view summing up all the rows above within the current view and this total dynamically changing as the view filters change.

The following image shows test data filtered by Vacant Employees and Position Leafs.
It returns 2 rows and what I would like to see is a third row which sums up the 2 rows for every column and when I change the filter it recalculates the total based on the new data returned to the view:

As can be seen in the image above I have no meaningful grand total that adds up the rows.

In PAX we can achieve this via dynamic reports which allows us to put Sum formulas at either the top or bottom of the report columns and this gives us grand totals.

Will the the new cube viewer allow for these grand totals or is there any plans to include this in a future update of PAW?

Personally, as things stand, I think it is a stretch to call PAW's calculation and summarising options powerful.
Adding in this feature would make that claim more plausible.

regards,

Mark

------------------------------
Mark Wragg
------------------------------
#PlanningAnalyticswithWatson

Hi George,

If the MDX against say the employee column is (the first row header column):

TM1FILTERBYLEVEL(TM1SUBSETALL([3000.00 Employee].[3000.00 Employee]) , 0)

then adding a Summary All will work, however, if the MDX is as follows (which is what I need to get the filters to work), it doesn't work when adding a Summary All:

STRTOSET([920.00 MDX Filters].(STRTOMEMBER("[9009.00 Clients].[9009.00 Clients]." + MEMBERTOSTR(STRTOMEMBER("[}Clients].[" + USERNAME + "]"))),[9999.16 Measures MDX Filters].[Employee Filter]))

So the thing I need to have the dynamic filters is the thing that prevents the Summary calculation appearing

regards,

Mark

------------------------------
Mark Wragg
------------------------------

Original Message:
Sent: Fri March 26, 2021 10:52 AM
From: George Tonkin
Subject: PAW Summary Totals for data in the current view

HI Mark,
Maybe I am missing something in your use case but would right-clicking the Row Header then Summarize all... not do what you need?
I know toggling to show total leading or trailing does not work on this but should give you a total line at the very least.

------------------------------
George Tonkin

Original Message:
Sent: Fri March 26, 2021 09:27 AM
From: Mark Wragg
Subject: PAW Summary Totals for data in the current view

Hi,

I am hoping the new cube viewer in PAW will address a current limitation which still necessities some PAX reports in our applications.
For our applications, it would be ideal if all our reports could be built in PAW, but the limitation outlined below is one reason why we still need to present some PAX sheets via PAW.

The limitation I speak of is as follows,

If I create a cube view, which allows the data to be filtered via MDX, I am not able to get a meaningful grand total for the view.
What I basically mean by this is a one line total row at the bottom of the view summing up all the rows above within the current view and this total dynamically changing as the view filters change.

The following image shows test data filtered by Vacant Employees and Position Leafs.
It returns 2 rows and what I would like to see is a third row which sums up the 2 rows for every column and when I change the filter it recalculates the total based on the new data returned to the view:

As can be seen in the image above I have no meaningful grand total that adds up the rows.

In PAX we can achieve this via dynamic reports which allows us to put Sum formulas at either the top or bottom of the report columns and this gives us grand totals.

Will the the new cube viewer allow for these grand totals or is there any plans to include this in a future update of PAW?

Personally, as things stand, I think it is a stretch to call PAW's calculation and summarising options powerful.
Adding in this feature would make that claim more plausible.

regards,

Mark

------------------------------
Mark Wragg
------------------------------
#PlanningAnalyticswithWatson

Mark-

We do something quite similar in our Workforce application utilizing very similar MDX.  We use "Aggregate" and the dynamic MDX works.  


I wonder if you could be experiencing an order of operation problem.  Have you tried putting your MDX into a public subset then using something like the following MDX.  Obviously, after you create the Aggregate (named "Total"?


 
The issue we have is that we have is that we have to use the PAX username function to get the name of the current as it is not available in PAW.

Chris 


------------------------------
Chris Courim
------------------------------

Original Message:
Sent: Fri March 26, 2021 11:26 AM
From: Mark Wragg
Subject: PAW Summary Totals for data in the current view

Hi George,

If the MDX against say the employee column is (the first row header column):

TM1FILTERBYLEVEL(TM1SUBSETALL([3000.00 Employee].[3000.00 Employee]) , 0)

then adding a Summary All will work, however, if the MDX is as follows (which is what I need to get the filters to work), it doesn't work when adding a Summary All:

STRTOSET([920.00 MDX Filters].(STRTOMEMBER("[9009.00 Clients].[9009.00 Clients]." + MEMBERTOSTR(STRTOMEMBER("[}Clients].[" + USERNAME + "]"))),[9999.16 Measures MDX Filters].[Employee Filter]))

So the thing I need to have the dynamic filters is the thing that prevents the Summary calculation appearing

regards,

Mark

------------------------------
Mark Wragg

Original Message:
Sent: Fri March 26, 2021 10:52 AM
From: George Tonkin
Subject: PAW Summary Totals for data in the current view

HI Mark,
Maybe I am missing something in your use case but would right-clicking the Row Header then Summarize all... not do what you need?
I know toggling to show total leading or trailing does not work on this but should give you a total line at the very least.

------------------------------
George Tonkin

Original Message:
Sent: Fri March 26, 2021 09:27 AM
From: Mark Wragg
Subject: PAW Summary Totals for data in the current view

Hi,

I am hoping the new cube viewer in PAW will address a current limitation which still necessities some PAX reports in our applications.
For our applications, it would be ideal if all our reports could be built in PAW, but the limitation outlined below is one reason why we still need to present some PAX sheets via PAW.

The limitation I speak of is as follows,

If I create a cube view, which allows the data to be filtered via MDX, I am not able to get a meaningful grand total for the view.
What I basically mean by this is a one line total row at the bottom of the view summing up all the rows above within the current view and this total dynamically changing as the view filters change.

The following image shows test data filtered by Vacant Employees and Position Leafs.
It returns 2 rows and what I would like to see is a third row which sums up the 2 rows for every column and when I change the filter it recalculates the total based on the new data returned to the view:

As can be seen in the image above I have no meaningful grand total that adds up the rows.

In PAX we can achieve this via dynamic reports which allows us to put Sum formulas at either the top or bottom of the report columns and this gives us grand totals.

Will the the new cube viewer allow for these grand totals or is there any plans to include this in a future update of PAW?

Personally, as things stand, I think it is a stretch to call PAW's calculation and summarising options powerful.
Adding in this feature would make that claim more plausible.

regards,

Mark

------------------------------
Mark Wragg
------------------------------
#PlanningAnalyticswithWatson

Hi Chris,

thanks for the response.

When I add in a sum or aggregate I know it exists because if I swap the rows and columns I can see it at the end of the columns. So I consider this a bug of some sort.

If I do as you suggest, then I can suddenly see the aggregation in the rows but it doesn't give me one row with a total as I would like, but returns a total for every row, so in effect I can suddenly see the totals but they are just a repeat of the rows in the view (see screen print below filtered for vacant employees):



If I only have one dimension in the rows, i.e. employee, then it sort of works but the issue then is how do you reference the user who is logged in?
I presume I would need to create public subsets for every user in the model in all relevant dimensions?

If I have a second column I need to include a consolidated element to get the total to work, but I also want to apply filters to this column, so I really want a view where I can apply filters to each column in the view and include this one line total.

In the image below when I add in a consolidated element to the second column and filter all employees whose name includes Terry, it does return with one total row but as I said, I also want to filter by this second column and don't necessarily want any consolidations included:


If you can get something to work with 2 columns in the rows, just at n level and allowing filters on those rows and including one row for the total I would be interested to see the solution!


regards,

Mark

------------------------------
Mark Wragg
------------------------------

Original Message:
Sent: Mon March 29, 2021 11:17 AM
From: Chris Courim
Subject: PAW Summary Totals for data in the current view

Mark-

We do something quite similar in our Workforce application utilizing very similar MDX.  We use "Aggregate" and the dynamic MDX works.  


I wonder if you could be experiencing an order of operation problem.  Have you tried putting your MDX into a public subset then using something like the following MDX.  Obviously, after you create the Aggregate (named "Total"?


 
The issue we have is that we have is that we have to use the PAX username function to get the name of the current as it is not available in PAW.

Chris 


------------------------------
Chris Courim

Original Message:
Sent: Fri March 26, 2021 11:26 AM
From: Mark Wragg
Subject: PAW Summary Totals for data in the current view

Hi George,

If the MDX against say the employee column is (the first row header column):

TM1FILTERBYLEVEL(TM1SUBSETALL([3000.00 Employee].[3000.00 Employee]) , 0)

then adding a Summary All will work, however, if the MDX is as follows (which is what I need to get the filters to work), it doesn't work when adding a Summary All:

STRTOSET([920.00 MDX Filters].(STRTOMEMBER("[9009.00 Clients].[9009.00 Clients]." + MEMBERTOSTR(STRTOMEMBER("[}Clients].[" + USERNAME + "]"))),[9999.16 Measures MDX Filters].[Employee Filter]))

So the thing I need to have the dynamic filters is the thing that prevents the Summary calculation appearing

regards,

Mark

------------------------------
Mark Wragg

Original Message:
Sent: Fri March 26, 2021 10:52 AM
From: George Tonkin
Subject: PAW Summary Totals for data in the current view

HI Mark,
Maybe I am missing something in your use case but would right-clicking the Row Header then Summarize all... not do what you need?
I know toggling to show total leading or trailing does not work on this but should give you a total line at the very least.

------------------------------
George Tonkin

Original Message:
Sent: Fri March 26, 2021 09:27 AM
From: Mark Wragg
Subject: PAW Summary Totals for data in the current view

Hi,

I am hoping the new cube viewer in PAW will address a current limitation which still necessities some PAX reports in our applications.
For our applications, it would be ideal if all our reports could be built in PAW, but the limitation outlined below is one reason why we still need to present some PAX sheets via PAW.

The limitation I speak of is as follows,

If I create a cube view, which allows the data to be filtered via MDX, I am not able to get a meaningful grand total for the view.
What I basically mean by this is a one line total row at the bottom of the view summing up all the rows above within the current view and this total dynamically changing as the view filters change.

The following image shows test data filtered by Vacant Employees and Position Leafs.
It returns 2 rows and what I would like to see is a third row which sums up the 2 rows for every column and when I change the filter it recalculates the total based on the new data returned to the view:

As can be seen in the image above I have no meaningful grand total that adds up the rows.

In PAX we can achieve this via dynamic reports which allows us to put Sum formulas at either the top or bottom of the report columns and this gives us grand totals.

Will the the new cube viewer allow for these grand totals or is there any plans to include this in a future update of PAW?

Personally, as things stand, I think it is a stretch to call PAW's calculation and summarising options powerful.
Adding in this feature would make that claim more plausible.

regards,

Mark

------------------------------
Mark Wragg
------------------------------
#PlanningAnalyticswithWatson