count the no of accts that had sales for the last 3years for each distiributor

[cognos05]

Hi,
I am using dimensional reporting and wanted to count the total no of accts that had sales for the last 3 years for each distributor.

My distributor hierarchy is like Region-Distibutor-Rep -Accounts.

I have dragged my Distributor level in my crosstab rows and in column I have a query calculation named total no of accounts.

Expression for Calculated column - Count([Qty] within set filter(Descendants(currentMember([Distributor Sales].[Ship To Customer].[Ship To Customer]),[Distributor Sales].[Ship To Customer].[Ship To Customer].[Customer]),tuple([Items],[Qty],[2015])>0))

The above query works, but I want to calculate for the last 3 years.. And how can I use this inside the tuple in the filter condition.

I appreciate your help.

Thanks,
Nithya

[cognos05]

Hi,

Inside my tuple, currently I have [2015] . but I need to have say for last 3 Years...

Can anyone let me know ,how can I solve this..Since I am counting the no of accounts that had sales for the products in the last 3 years , I need to use tuple
Count([Qty] within set filter(Descendants(currentMember([Distributor Sales].[Ship To Customer].[Ship To Customer]),[Distributor Sales].[Ship To Customer].[Ship To Customer].[Customer]),tuple([Items],[Qty],[2015])>0))


Now instead of [2015] I will have something like lastperiods(5,[member])..

Any suggestions are appreciated.

Thanks,
Nithya

[TheFrenchGuy]

Hello,

you can try

Code Select Expand

Lastperiods(3,item(tail(year),0))

[cognos05]

Can I add this inside the tuple ? I dont think it would work , this would return a set right ?

[TheFrenchGuy]

Have you tried ? ^^

[cognos05]

Yes, I have tried..member to member set coercion error..

[TheFrenchGuy]

You can try

Code Select Expand

lastPeriods(3,lastChild(MUN Year))

[cognos05]

The expression you suggest will work normally, but not when used inside a tuple .

Count([Qty] within set filter(Descendants(currentMember([Distributor Sales].[Ship To Customer].[Ship To Customer]),[Distributor Sales].[Ship To Customer].[Ship To Customer].[Customer]),tuple([Items],[Qty],[2015])>0))

Here instead of 2015, I need to show the last 24 months.
so I already have an expression which gives me 24 months, bit its a member set type and tuple will expect only member.so I need a way to solve this.

Thanks,
Nithya

[TheFrenchGuy]

Count for 2015 + Count for 2014 + Count for 2013 ?

[cognos05]

Right now I have used
instead of 2015 - I created a member with expression - [currentYear]+lag(currentYear,1)+lag(currentYear,2) 

and replaced this data item inside the tuple for time.

but if its going to be months , then this expression will be too long..
so I was asking if there is different approach to solve this.

Thanks,
Nithya

[TheFrenchGuy]

Maybe use the running-total function but don't manage so good.

Let me know if you want it

[cognos05]

Yes, let me give it a try. Please let me know on how to use it.If it needs to be done at the db level then I think I would need to see other approaches to achieve the same.

[cognos05]

Hi ,

I am using the below code to count the number of accounts under a distributor which had sales for the last 24 months.

I am using below data item
Count([Qty] within set filter(Descendants(currentMember([Distributor Sales].[Ship To Customer].[Ship To Customer]),[Distributor Sales].[Ship To Customer].[Ship To Customer].[Customer]),tuple([Items],[Qty],[2015])>0))


In the tuple, I have hard coded [2015] and it works, but I wanted to use the last 24 months from current month.

lastPeriods (25,#'[Distributor Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#)

but when I use this I get member to member set coercion.

So I had to manually create a data item and add ( [jun 2014]+jul 2014] +....Jun[2016]
and if I add this member to the tuple it would work..

Instead of adding each month, is there a way to do something..

Please let me know your suggestion guys.

[TheFrenchGuy]

Hello,

on the year exemple, you can try :

Code Select Expand

total([Measure] within set lastPeriods (3,lastChild([Year MUN])))

It works for me and provides quantities (measure I've chose) for last 3 years.

From this formula, you can easily make it in order to split by months if you have a Month level.

[cognos05]

Hi,
Thanks for your suggestions and I appreciate your time taken to help me out.

I have  Distributor in my rows (which is a level in my hierarchy )and my column has Total no of accts that had sales for last 3 years.
Each distributor will have n accounts which will be the lowest level of that hierarchy .

say my distributor A had 45 accounts and out of 45 only 25 had some qty sold then I will have 25 against Distributor A.


I am not doing the total no of quantity, whereas I am counting the no of accts under a distributor that had any sales for the last 3 years.

So the expression you have given will calculate the total quantity sold for a distributor in the given months.

So I will have to include a children (currentMember) to go the accounts and use count.

Thanks,
Nithya

[TheFrenchGuy]

Can you pls provide your crosstab sample in order to see exactly the structure ?

The formula you're trying to set up could be fill in to columns, when distributors are in line.

[cognos05]

Hi,

Hierarchy for the dimension is  -  Region->Distributor-?SubRegion->Rep->Accounts.
Also find the attached image of the CrossTab.

Thanks,
Nithya