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
Quote from: nithya1224 on 20 Jan 2015 03:53:57 PM
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.
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.
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
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..
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