countif

[Manu0521]

I am using dimensional reporting.
I have a cross tab with column having dynamic months with respect to current month.

Sales               August2014  Sep2014 Oct2014   Nov2014   Dec2014  Jan2015      Count the last transacted sales

ABCD                0                    5              5               5              4             4                   This should give me 6 as the last transacted sale month

so count should start from current month and count until it gets the first zero and skip after that.

I feel getting this expression is very tricky.because I have to tell count until you get the qty value as zero.

Thanks,
Manu

[keshavchhn]

You can use a filter to get value >0 and then apply count.

[Manu0521]

Hi All,

I came up with a solution, but its taking much time to load data

Say I have 24 months in my column and Accounts nested under Sales reps in my rows.The measure being quantity.

I will have to calculate the no of months on which there was a last sale in these 24 months.

1) I sorted all my 24 months starting jan 2015.

Then Used the below expression.
if(tuple(item([Last2Years],0),currentMember([Sales].[Ship To Customer].[Ship To Customer]),[Qty])<>0)then(1)
Else if(tuple(item([Last2Years],1),currentMember([Sales].[Ship To Customer].[Ship To Customer]),[Qty])<>0)then(2)
Else if(tuple(item([Last2Years],2),currentMember([Sales].[Ship To Customer].[Ship To Customer]),[Qty])<>0)then(3)
Else
(0)

I traversed through all 24 items using 24 if else expression and then found the last sold qty month.

Is there any other dimensional function which would allow me to do this without too many if else.

Your suggestions are appreciated.

Thanks,

[Manu0521]

Hi,

Is there an alternate workaround for this.

Thanks,
Manu

[keshavchhn]

this could be easily done if you can filter the value > 0 and then apply count for current measure.

[Manu0521]

Hi Keshavchhn,

Can you tell me the expression for the data item, how will you avoid the if else and how will skipping occur.Can you explain me on how to achieve this.

Thanks,
Manu

[keshavchhn]

Suppose you have a measure 'Revenue' a product type as row and Month in the column.
You need to apply a filter so that you can get all the value >=0 then apply the count for Month columns
"count(currentMeasure within Set [Month])" this will give you count of months but you also need to add one data item for current month before Count and then add "1" into existing count to get the correct number.