I did not see any problem in previous versions so I do not think that will help.
I suspect the problem might be due to the Week dimension that you added to the model! From this post:
"A week level in the same hierarchy as day and month will lead to unexpected results. If a week period is needed the week level should be placed in a different hierarchy than the rest of the levels."
I did not see any problem in previous versions so I do not think that will help.
I suspect the problem might be due to the Week dimension that you added to the model! From this post:
"A week level in the same hierarchy as day and month will lead to unexpected results. If a week period is needed the week level should be placed in a different hierarchy than the rest of the levels."
One way to do this is by using a pivot variable. Create the same pivot variable "Region" in both pivots on which your widgets are based. These pivot variables should return the members, in your example Asia, Europe, N. America, S. America. You can define the manually or in a termlist, or use a kpi to retrieve them.
For the example in the screenshot below I created a HoleFood2 cube with a Outlet2.H1.Region2 level. This level is "incompatible" with an Outlet.H1.Region level in HoleFoods. In my manual Region pivot variable I simply defined two regions, which can be selected manually.
Once you have these two pivot variables create a calculated dimension on each pivot using the pivot variable. In your example in HoleFoods the expression should be Outlet.[$variable.Region]. Place the calculated dimension on Filters.
This is how I did it in HoleFoods:
and this is how I did it in HoleFoods2:
Finally, add an ApplyVariable control on one of your widgets with "*" as target. Selecting a region will filter both widgets.
That is what I understood from your original post. The query I suggested in my first answer works should do and it works for me, see the screenshot. If it doesn't and you are sure you have a last day of the month with revenue in your data try to troubleshoot it by simplifying the query. For example, start by removing .LASTCHILD from the calculated dimension.
The query I posted should return the revenue on the last day of a month (for example 31 Jan 2017, 28 Feb 2017, 31 March 2017). Maybe your data does not have any revenue on some last day of the month.
I probably misunderstood your questions. In particular these questions you wrote: "How can I manage to show in MDX query months where every month shows the value of the last month?"
"But what MDX will show the months with the revenue for the last day in a month?"
%LAST evaluates a measure or numeric expression for the last non-empty member of a set, so I do not think that is what you want.
I am using %LASTCHILD, but you cannot append it to [DateOfSale].[Actual].[MonthSold].Members because %LASTCHILD only works on specific member. For the moment I found this solution:
WITH MEMBER [DateOfSale].[LastDayOfMonth] AS '[DateOfSale].[Actual].[MonthSold].CurrentMember.LASTCHILD'
SELECT [Measures].[Amount Sold] ON 0,
NON EMPTY NONEMPTYCROSSJOIN([DateOfSale].[Actual].[MonthSold].Members,[DATEOFSALE].[LASTDAYOFMONTH]) ON 1 FROM [HOLEFOODS]
The resulting headers are ugly and I have not yet been able to successfully use [DateOfSale].[Actual].[MonthSold].CurrentMember.Properties("Name")
I do not think there is a way to ask the user to select a user before displaying the dashboard.
Maybe you can try setting the filter as required in the dashboard settings and as default value using a run-time variable. The run-time variable should return the current username. To return the current user start from the COS $username special variable.
You can use an applyFIlter or setFilter control and under Filter select "Named Filters". In the resulting control you will be able to select the named filters you defined on your pivot.
In the options for pivot table you can use Count instead of Grand Total as Summary. However, you will have to choose between the Grand Total and Count. If you calculate the grand total in some other way (for example by placing another [Outlet].[H1].[Region].Members and then Gear icon > Compute Aggregate > SUM), that COUNT will also count the cell with the Grand Total.
Otherwise you can create a "Row Number" calculated measure with the expression below and place it on measures next to Revenue
If you are asking how to show the number of rows in a given DeepSee pivot, edit your current MDX and wrap the expression on rows in the COUNT function.
If you are asking how to limit the number of rows, click on the gear on the Rows box and use the "Return the first n members" checkbox.
Yes, there is currently a bug with pivot variables and calculated member using shared storage. Let's hope this bug will get fixed soon. I replace the screenshots which include ISNULL in the expression
Yes, in this example it makes sense to use %OR instead of AGGREGATE. AGGREGATE can become useful when we the user needs to bring a measure inside the calculated dimension, for example to avoid crossjoining or having two measures in different axes. Also note that in the next major version an AGGREGATE function that acts on a set (first argument) with no expression (second argument) will be automatically replaced at the parser level with %OR for the purposes of calculating results. Thank you for your suggestion, I updated the post.
I suggest we discuss these issues in a WRC or TRC. This is the link to Support.
For example, we might want to look into some of these concept: Time vs Data Dimensions, sorting the members in a level from Architect, and the "Depends on" setting for levels that can be set in Architect.
This is definitely not a newbie question, and ParallelPeriod could be very useful for the MDX you need. However, your pivot table has to be dynamic enough to work with different periods (Quarter, Month, Week). For this reason we might have to work on a KPI. I have two questions:
1) If I understand you correctly you want to be able to select any period (such as Jan-2014 or Week 2 of 2015), not only the current one (such as Nov-2016 or Q4-2016). Is that correct?
2) If so, what filters you like to appear on your dashboard? For example, would you like to have a dropdown menu to select Quarter/Mont/Week and a "dynamic" filter showing a Quarter/Month/Week for all years in your data?
DeepSee models are based on cubes, which define elements such as Measures, Dimensions, Listings, etc. The hierarchical structure in your question can be modeled using a Dimension containing one hierarchy with three levels. The levels in the hierarchy are, in order, the Group, Name, and Code levels. Once you define this model in Architect you will be able to use the hierarchical structure in DeepSee Analyzer.
I recommend reading section 2.2 "Dimensions, Hierarchies, and Levels". There you will find an example based on the Patients cube where an Age dimension (AgeD) includes one hierarchy with three levels ordered from the least to the most granular:
I also recommend reading section 4.6 "Defining Hierarchies Appropriately".
For example, say that your pivot table based on HoleFoods filters on a named set which selects a single City, and you defined in Architect/Studio dependsOn="[Outlet].[H1].[City]" for the Product Category level of holeFoods. An applyFilter control on Product Category still show all the members for Product Category.
go to post
I did not see any problem in previous versions so I do not think that will help.
I suspect the problem might be due to the Week dimension that you added to the model! From this post:
"A week level in the same hierarchy as day and month will lead to unexpected results. If a week period is needed the week level should be placed in a different hierarchy than the rest of the levels."
go to post
I did not see any problem in previous versions so I do not think that will help.
I suspect the problem might be due to the Week dimension that you added to the model! From this post:
"A week level in the same hierarchy as day and month will lead to unexpected results. If a week period is needed the week level should be placed in a different hierarchy than the rest of the levels."
go to post
One way to do this is by using a pivot variable. Create the same pivot variable "Region" in both pivots on which your widgets are based. These pivot variables should return the members, in your example Asia, Europe, N. America, S. America. You can define the manually or in a termlist, or use a kpi to retrieve them.
For the example in the screenshot below I created a HoleFood2 cube with a Outlet2.H1.Region2 level. This level is "incompatible" with an Outlet.H1.Region level in HoleFoods. In my manual Region pivot variable I simply defined two regions, which can be selected manually.
Once you have these two pivot variables create a calculated dimension on each pivot using the pivot variable. In your example in HoleFoods the expression should be Outlet.[$variable.Region]. Place the calculated dimension on Filters.
This is how I did it in HoleFoods:
and this is how I did it in HoleFoods2:
Finally, add an ApplyVariable control on one of your widgets with "*" as target. Selecting a region will filter both widgets.
go to post
That is what I understood from your original post. The query I suggested in my first answer works should do and it works for me, see the screenshot. If it doesn't and you are sure you have a last day of the month with revenue in your data try to troubleshoot it by simplifying the query. For example, start by removing .LASTCHILD from the calculated dimension.
go to post
The query I posted should return the revenue on the last day of a month (for example 31 Jan 2017, 28 Feb 2017, 31 March 2017). Maybe your data does not have any revenue on some last day of the month.
I probably misunderstood your questions. In particular these questions you wrote:
"How can I manage to show in MDX query months where every month shows the value of the last month?"
"But what MDX will show the months with the revenue for the last day in a month?"
Could you clarify?
Thanks
go to post
%LAST evaluates a measure or numeric expression for the last non-empty member of a set, so I do not think that is what you want.
I am using %LASTCHILD, but you cannot append it to [DateOfSale].[Actual].[MonthSold].Members because %LASTCHILD only works on specific member. For the moment I found this solution:
WITH MEMBER [DateOfSale].[LastDayOfMonth] AS '[DateOfSale].[Actual].[MonthSold].CurrentMember.LASTCHILD'
SELECT [Measures].[Amount Sold] ON 0,
NON EMPTY NONEMPTYCROSSJOIN([DateOfSale].[Actual].[MonthSold].Members,[DATEOFSALE].[LASTDAYOFMONTH]) ON 1 FROM [HOLEFOODS]
The resulting headers are ugly and I have not yet been able to successfully use [DateOfSale].[Actual].[MonthSold].CurrentMember.Properties("Name")
go to post
I do not think there is a way to ask the user to select a user before displaying the dashboard.
Maybe you can try setting the filter as required in the dashboard settings and as default value using a run-time variable. The run-time variable should return the current username. To return the current user start from the COS $username special variable.
go to post
You can use an applyFIlter or setFilter control and under Filter select "Named Filters". In the resulting control you will be able to select the named filters you defined on your pivot.
go to post
In the options for pivot table you can use Count instead of Grand Total as Summary. However, you will have to choose between the Grand Total and Count. If you calculate the grand total in some other way (for example by placing another [Outlet].[H1].[Region].Members and then Gear icon > Compute Aggregate > SUM), that COUNT will also count the cell with the Grand Total.
Otherwise you can create a "Row Number" calculated measure with the expression below and place it on measures next to Revenue
COUNT(%CELL(-1,0))-1+%CELL(0,-1)
go to post
If you are asking how to show the number of rows in a given DeepSee pivot, edit your current MDX and wrap the expression on rows in the COUNT function.
If you are asking how to limit the number of rows, click on the gear on the Rows box and use the "Return the first n members" checkbox.
go to post
Yes, there is currently a bug with pivot variables and calculated member using shared storage. Let's hope this bug will get fixed soon. I replace the screenshots which include ISNULL in the expression
go to post
Hello Evgeny,
Yes, in this example it makes sense to use %OR instead of AGGREGATE. AGGREGATE can become useful when we the user needs to bring a measure inside the calculated dimension, for example to avoid crossjoining or having two measures in different axes. Also note that in the next major version an AGGREGATE function that acts on a set (first argument) with no expression (second argument) will be automatically replaced at the parser level with %OR for the purposes of calculating results.
Thank you for your suggestion, I updated the post.
go to post
Hello Samuel,
I posted an article on Developer Community with a possible solution for this implementation. Please check this article.
Thanks
go to post
Hello Benjamin,
I suggest we discuss these issues in a WRC or TRC. This is the link to Support.
For example, we might want to look into some of these concept: Time vs Data Dimensions, sorting the members in a level from Architect, and the "Depends on" setting for levels that can be set in Architect.
Thanks
go to post
Hello Samuel,
This is definitely not a newbie question, and ParallelPeriod could be very useful for the MDX you need. However, your pivot table has to be dynamic enough to work with different periods (Quarter, Month, Week). For this reason we might have to work on a KPI. I have two questions:
1) If I understand you correctly you want to be able to select any period (such as Jan-2014 or Week 2 of 2015), not only the current one (such as Nov-2016 or Q4-2016). Is that correct?
2) If so, what filters you like to appear on your dashboard? For example, would you like to have a dropdown menu to select Quarter/Mont/Week and a "dynamic" filter showing a Quarter/Month/Week for all years in your data?
go to post
Hello Alexandre,
Yes, DeepSee supports hierarchical structures.
DeepSee models are based on cubes, which define elements such as Measures, Dimensions, Listings, etc. The hierarchical structure in your question can be modeled using a Dimension containing one hierarchy with three levels. The levels in the hierarchy are, in order, the Group, Name, and Code levels. Once you define this model in Architect you will be able to use the hierarchical structure in DeepSee Analyzer.
This is the link to a video in the Video Portal. The video I link explains how to define Dimensions in DeepSee cubes: http://video.intersystems.com/video/Video.Pages.VideoLibrary.cls?video=2663141302001&playlistid=2621105743001
This is the link to the "Defining DeepSee Models" book in the documentation: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2MODEL
I recommend reading section 2.2 "Dimensions, Hierarchies, and Levels". There you will find an example based on the Patients cube where an Age dimension (AgeD) includes one hierarchy with three levels ordered from the least to the most granular:
I also recommend reading section 4.6 "Defining Hierarchies Appropriately".
go to post
Thank you for the tip.
This problem never happened to me so I am curious to hear what is the output of
w ##class(%DeepSee.Utils).%BuildCube("CubeName")
is when the problem occurs.
To understand the underlying cause of the problem is there any way this problem can be reproduced?
go to post
Hello David,
I wanted to suggest to use the DependsOn feature (see http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...) but I noticed that that does not work with named filters.
For example, say that your pivot table based on HoleFoods filters on a named set which selects a single City, and you defined in Architect/Studio dependsOn="[Outlet].[H1].[City]" for the Product Category level of holeFoods. An applyFilter control on Product Category still show all the members for Product Category.