Last Sold Month

[cognos05]

Hi All,

I am using Dimensional Reporting.
I have Accounts in my cross tab rows and a Product named B nested to it , in my column I have a data item with an expression which will retrieve last 12 months from time dimension.

Expression for the column data item is "lastPeriods (12,#'[Sales].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#)"

I have Qty in Measure

1) Say my data looks like
                  May2014  Jun2014 Jul2014   Aug2014   Sep2014  Oct2014  Nov2014  Dec2014 Jan2015    Total     Last Sold       Status
10506   B      5               0             0                 0             0              0               0             0            0              5        May 2014      InActive
10507   B      0               0             0                 1             0              0                2             0            0             3         Nov 2014       Active
10508   B      1               1              1                 0             0              0                0              0          0             3          Jul 2014        InActive

I am using a column called Last sold which will tell me when the product was last sold.
In the above example the last sale for Account 10506 , Product B was May 2014.
How do I get the Last sold Month, should I use any dimensional function or how would the data expression look for this.
If all the months has 0 Qty then Last Sold will be empty

Thanks,
Nithya

[MFGF]

Hi All,

I am using Dimensional Reporting.
I have Accounts in my cross tab rows and a Product named B nested to it , in my column I have a data item with an expression which will retrieve last 12 months from time dimension.

Expression for the column data item is "lastPeriods (12,#'[Sales].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#)"

I have Qty in Measure

1) Say my data looks like
                  May2014  Jun2014 Jul2014   Aug2014   Sep2014  Oct2014  Nov2014  Dec2014 Jan2015    Total     Last Sold       Status
10506   B      5               0             0                 0             0              0               0             0            0              5        May 2014      InActive
10507   B      0               0             0                 1             0              0                2             0            0             3         Nov 2014       Active
10508   B      1               1              1                 0             0              0                0              0          0             3          Jul 2014        InActive

I am using a column called Last sold which will tell me when the product was last sold.
In the above example the last sale for Account 10506 , Product B was May 2014.
How do I get the Last sold Month, should I use any dimensional function or how would the data expression look for this.
If all the months has 0 Qty then Last Sold will be empty

Thanks,
Nithya

Hi,

I spot a fundamental flaw in your design here - you can only display measures in crostab cells. You're not going to be able to display month members in the cells as you desire.

MF.

[Robl]

Well, you can, sort of.
You'll need to set the last sold month as a measure within Transformer (or fm dmr) and give it a roll up type of maximum rather than sum.
It'll effective be showing as a big number, so you'll need to store it like 201403 rather than 2014 March, then do something with the formatting to show it as you want it.

Alternatively you could put the last month on the left before the metrics.
I don't think you can put any sort of crosstab space on the right of the metrics with attributes or dims, but I'm happy to be corrected.

[cognos05]

Thanks MFGF,

Is there a way to achieve the same by counting the number of months the Qty measure was last bought.
Example

July 2014 Aug 2014 Sep 2014 Oct 2014 Nov 2014 Dec 2014 Jan 2015    Last Bought
       0            3              0               0                0          0               0                   6
       0           4               0               0                0          0               3                   1
       0           0               0               0                0          0               0                   0

so this should give me the No of months since last bought,
Is this possible ..
Expression for the column data item months used is "lastPeriods (12,#'[Sales].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#)"


Thanks,
Nithya

[Manu0521]

May be use count of current measure with in the set of given months and a condition to stop if there is any quantity in the month..

[cognos05]

Hi,
I need the expression for conditional if to count the last sold month starting current month.skip the count at the point where the qty of month is greater than zero..

Any help is appreciated.

Thanks,
Nithya