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
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
Hello,
you can try
Lastperiods(3,item(tail(year),0))
Can I add this inside the tuple ? I dont think it would work , this would return a set right ?
Have you tried ? ^^
Yes, I have tried..member to member set coercion error..
You can try
lastPeriods(3,lastChild(MUN Year))
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
Count for 2015 + Count for 2014 + Count for 2013 ?
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
Maybe use the running-total function but don't manage so good.
Let me know if you want it
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.
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.
Hello,
on the year exemple, you can try :
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.
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
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.
Hi,
Hierarchy for the dimension is - Region->Distributor-?SubRegion->Rep->Accounts.
Also find the attached image of the CrossTab.
Thanks,
Nithya