If you are unable to create a new account, please email bspsoftware@techdata.com

Author Topic: Cognos Dynamic Cube 10.2.2 -Inventory Cube - Time Sate Aggregation Rule - Last  (Read 10529 times)

Offline GeethaKL

  • Community Leader
  • *****
  • Join Date: Apr 2015
  • Posts: 86
  • Forum Citizenship: +1/-0
Hello All,

I have a Inventory Cube where the granularity of the Fact Inventory is at the day level and we have data monthly for the previous months and for the current month it is weekly data.
We have a  requirement to show last 12 months data including the current month. For the current month it should show current week's data.

Currently its showing the aggregate value for the current month.
For example if it is first week .. showing first week value,
                           second week - showing first+second week((should show only  second Week's value)
                           Third Week - showing first +Second + Third Week(should show only Third Week's value)

I applied "Time State Aggregation Rule - Last" in cube designer and its only displaying data if the data is at the last day of the month. for other months its coming up as null.

Please advise me how to achieve current week's data in this scenario and Where exactly I am going wrong?

Thanks in advance

Regards
Geetha

Offline GeethaKL

  • Community Leader
  • *****
  • Join Date: Apr 2015
  • Posts: 86
  • Forum Citizenship: +1/-0
Looks like its not possible if we dont have data in the last day of the month.

Trying another option in cube designer. Applying filter in the Measure section
1. If it is previous months for the year, it is monthly figure, if it is current month, show current weeks data
     month(datekey)< month(currentperiod) or datekey = current period.
Now I am able to see current week's data for the current month which os good so far.
However, I have two other requirements:

1.  if I click current month(for ex: Sep 2019), to show all the two weeks data so far, I can see only Second week's data which is incorrect. I should see all the weeks so far.
2. In the total , i have to see only last period data. currently its totalling all the months data.

Please advise me if anyone come across thee issues.

Thanks in advance
Reg
Geetha

Offline GeethaKL

  • Community Leader
  • *****
  • Join Date: Apr 2015
  • Posts: 86
  • Forum Citizenship: +1/-0
Hi
Can anyone suggest how to get last period data for Inventory in Analysis Studio , the cube that developed in Cognos Cube designer.
For example,: If I click Year, it should display the most recent month data in the total
If I click the month, iit should display the most recent week's data in the total.

Please suggest me

Thanks in advance

Regards
Geetha

Offline GeethaKL

  • Community Leader
  • *****
  • Join Date: Apr 2015
  • Posts: 86
  • Forum Citizenship: +1/-0
Hello All,

I am not really sure whether you did understand my question below or quite busy.

Looks like I have got a solution. Still investigating though.


WORK AROUND:

In Cognos Cube Designer, Under Measure section, clicked aggregation rules --> selected a particular measure-->  and selected a dimension, then given Last.
Also in the filter section for that particular dimension, applied filter as : Measure > 0
Published the cube.

Now, In Analysis Studio, when I drag Measure in the measure section, Dimension in to the column area, its working exactly as per my requirement. Getting Last period data for the Current Month and in the total --> current period data(instead of total Amount). When I clicked the current month getting all the weeks data and again in the total column, getting current week data.

Regards
Geetha