Insight - how to get months and new column after crosstab

[sunil-yadav]

Hi,

I am working on a report in which I have to show capacity utilization from Jan-17 to Jan-18 but I am facing some challenges. Below are the queries:

1. How should I get 13 months data based on last day of the month? I used [Full Date] between _add_months(current_date,-13) and _add_months(current_date,-1)

    But that show data captured at first of each month, where as customer need data consumed at the end of month.

2. I build a cross tab to populate data from Jan-17 to Jan18. But there is requirement to add 2 more columns at end which show difference between 13 months in terms of capacity and utilization %. I can calculate it from regular data items but how can I pick specific months from cross tab which generate data at runtime and do calculation based on that.

3. Internal Volume DM does not have any data item of total capacity, so how can I calculate utilization %. Because allocated and consumed capacity in that DM both show same values. 

Thanks

[hoffman]

Hi Sunil,

For question 1, cognos has a _last_of_month function, so for instance you can pick out the last day of the previous month with _last_of_month(_add_months(current_date,-1))

For number 2, I've never worked out a way to get cognos to calculate a difference in cross tabs - you can do average, total, etc, but not difference as near as I can tell. Maybe create a separate data item in the query and then display that?

For number 3: allocated capacity = consumed capacity if the internal volume is NOT thin provisioned. For thin provisioned volumes, those two numbers should not be equal and you can divide consumed by allocated to get a % full number. For thick provisioned internal volumes, you can look at the difference between used capacity and allocated to see the amount of 'trapped' capacity.

[sunil-yadav]

Hi Hoffman,

Thanks for quick response.

1. I have tried _last_of_month function but it returned same result i.e. showing first of every month. Not sure if I wrote syntax wrong or what. 

      [Full Date] between _last_of_month(_add_months(current_date,-14)) and _last_of_month(_add_months(current_date,-1))

2. I know adding data item is the way. Even if I create a data item in query explorer, I cannot append that in page after crosstab. Getting error "This object can only be inserted into a data container". Do I have to create a new block for that? If yes, how I make sure that calculation done in that data item relates to specific volume and for which month?

3. Used capacity is showing 0GB for all internal volumes, where as customer is saying that all volumes are in use. And what's the difference between used and data used capacity?

[moechnig]

I haven't used _last_of_month (Thanks Brian!), but one isssue you will encountere here is that daily data will only be retained for 1 year.  If I'm reading your question correctly, you actually need 13 months.  Oh, also, make sure you're using the daily stats, not the monthly stats, since the monthly ones will aways be the first measurement of each month.