Hiding categories in powerplay with calculations

[Sefke]

First time poster, so please bear with me.

We have a powerplay (explorer) report, which contains a date dimension with 40 months. However for most reports we only wish to display the last 6 months. We also have accumulated calculations based on 2 measures and some further calculations based on these accumulated values. We have found that deleting some of the date categories in powerplay changes the accumulated calculation results, which I can understand. However we now also see that hiding some date categories has the same affect. Even minimising the column width and dragging the column marker past the previous column marker, changes the calculations.

Is there any other way to hide, suppress ( I can't use transformer as other reports do require all date categories, unless I create a sub-cube) or delete these categories without affecting the outcome of the calculations?

Thanks in advance for your help,

Sef
(Powerplay 7.1.341.2)

 

[BruceReed]

I am not certain I understand you correctly, but here goes...

Make a new manual date dimension in transformer called "Last 6 months". Look at the pdf "Step by step transformer" for more details on how to make this manual dimension. You will have to make sure that you include or exclude the current month as you see fit. (Usually the current month is only partially completed)

Once you get the dimension in, include this relative date in the PP report(s), and take off the actual months from the report.

Once this is done, the report is automatic, last 6 months will be constantly moving for you.

Hope this helps
Bruce

 

[Sefke]

Hi Bruce

Thank you for the info.
I haven't tried your method for two reasons. I mentioned that we have a time dimension, but in real terms it is not a genuine time dimension. The fields in the impromptu reports are merely 'Indicators of time' and are just strings.
Secondly, even if the dimension was a real time dimension, by applying the 'last 6 month' filter, the accumulation would miss previous values. I'll try and give a simplified scenario:

1 July 2000 our total stock is 1000
July 2000 transactions are +100
Therefore my July 2000 value (closing stock) is 1100
Transactions in subsequent months, with their accumulated values are:
Jul 2000 1100 1100
Aug 2000 -50 1050
Sep 2000 +10 1060
Oct 2000 -20 1040
Nov 2000 +30 1070

If I now hide for example Aug 2000 and Sep 2000, my powerplay report will look as follows:
Jul 2000 1100 1100
Oct 2000 -20 1080
Nov 2000 +30 1110

My question is, how can I hide Sep 2000 and Oct 2000 from view, without affecting the accumulated totals.

Rgds, Sef

 

[BruceReed]

Hello Sef

OK now I understand what you are trying to do.

It's fairly easy to do it. Using Impromptu OR transformer create a new calculated date field, using the function Last-of-month (of the transaction date). Create this new field in which ever program you find easier to work with, transfomer can work with either type.

Now using transformer, break out the date dimension using this calculated date field & not the original date. Regardless of whether you list this date or not in PP, the quantity total (for each month end) will have the right amount.

It sounds like you will have to get creative with your strange date fields. I suggest you catenate your strings in Impromptu to get a real date field, before creating the last-of-month function.

Hope this steers you down the right path.
Bruce

 

[Sefke]

Hi Bruce

Are you saying that purely because my 'pseudo' date bucket is not a real time dimension, powerplay behaves like it does and treats a hidden level like a deleted level?
I can convert my pseudo time buckets into real time dimensions, but since I have 6 different data sources in my model and a single build takes 12 hours, I won't be able to do this until hopefully next week.

Thanks again for your input.
Rgds, Sef

 

[BruceReed]

Hello Sef
When you use time dimensions in Cognos all of the calendar rules are in place for you. This means that there is always a January, followed by Feb., etc., so even if there is no activity for a time period, Cognos implicitly recognizes a missing time period as zero. When you run the date wizard in transformer, and you have only one entry for 2003, Cognos will normally create all the categories for the entire year.

When you make a report based on cumulative values for the year, what you did in March is critical to the number in September. If you have a time dimension of month end, Cognos is keeping track of every month, even if you don't need to view the data. In regular categories, this logic is not there for you, so you get your cumulative values on whatever rows/columns are showing.

I suggest that you add a time dimension in transformer (you can still do it in Impromptu, but you would have 6 separate reports to change) as I said above. To force the issue (making sure Cognos knows that your field is a date & not text), make sure that the column in the data source field is set to Date Class Date, then add the new field last-of-month function on the original field.

Before running a build for 12 hours, why not use the "test build" button under the run menu? This way you could tweak & test quickly.

Sorry that my reply is long winded, but I hope this helps. Post back if you have any more questions.

Bruce

 

[Sefke]

Hi Bruce

Thanks again for taking the time to reply to my query.

I have now created a sand pit model and have tried to validate the results using 'real' time dimensions. Unfortunately hiding individual time dimension levels in powerplay still changes accumulated calculations (just to re-iterate, the accumulations are done in powerplay and not transformer). Is there maybe some way to accumulate measures in transformer (not just the default accumulation that displays like a grand total at the end, but more like an accumulation to-date)?

Unfortunately I am back to square 1. Are there any other suggestions out there, or am I still missing something?

Rgds, Sef

 

[BruceReed]

All measures for every dimension are done in transformer. PP just does calculations on what it sees on the screen.

I am not sure where you get the original starting inventory number. Is that from a physical inventory at the start of the year & never changes, or an inventory number direct out of the MRP/ERP system & is constantly refreshed? Is it constantly calculated for you some were? I assume not as this is what you are trying to find out.

You will have to make sure that you calculate EACH month end inventory position BEFORE PP. The original app or Impromptu, or transformer is the place to do it.

Transformer always accumulates every measure, for every dimension. See the on-line docs under roll-ups for more details. Setting up the correct measures & dimensions can be very tricky, but once done, PP is just the viewer.

Cheers
Bruce

 

[Sefke]

Hi Bruce

It's been a little while since reviewing my issue, but in the mean time I got another suggestion, which looks like working. I guess this is an approach you suggested yourself initially, but I just needed to think that one extra step outside the square to get to it.
I have now created new categories in my pseudo time dimension, where new category 'Jul 00' only contains July 2000, 'Aug 00' contains July 2000 and August 2000, 'Sep 00' contains July 2000 to Sept 2000 etc etc until category 'Aug 03' which contains July 2000 to Aug 2003. I also supressed all original time categories.
All values in this new pseudo time dimension are now already accumulated and no accumulation is required in powerplay. I realise that with real time dimensions I could use relative times and offsets, but for the time being I am confident the method will work.

Thanks again for your input,

Kind Regards, Sef

 

[BruceReed]

Sef

Just glad to hear it's now working for you.

Bruce