Dual total in YTD

[Saro_2306]

Hi All,

I need to combine below 2 formulae as one formulae for calculation Target (i.e Target = Prod Units / Working Days). it should be shown in Bar chart with dimension User Name. 

For all users Target should be same. 

sum({<[Week Num]={">=$(=(week(Yearstart(Max([Week Num]))))) <=$(=Max([Week Num]))"}>} TOTAL<Desk>[Production Units]) ,

sum({<[Week Num]={">=$(=(week(Yearstart(Max([Week Num]))))) <=$(=Max([Week Num]))"}>} TOTAL<Desk>[Working Days Count])

Need your help. Thanks in advance.

[agigliotti]

Hi @Saro_2306 ,

Maybe this:
=sum( {< [Week Num] = {">=$(=(week(Yearstart(Max([Week Num])))))<=$(=Max([Week Num]))"}>} TOTAL<Desk> [Production Units]/[Working Days Count] )

I hope it can help.

Best Regards

[Saro_2306]

@agigliotti  Not working

[agigliotti]

why do you need a total for <Desc> field ?

if you have the user name as dimension and the result is the same for all users, try the below:

=sum( {< [Week Num] = {">=$(=(week(Yearstart(Max([Week Num])))))<=$(=Max([Week Num]))"}>} TOTAL [Production Units]/[Working Days Count] )

[Saro_2306]

I am having set data of 4 teams and 4 weeks. I need to add target of particular team. So i had used TOTAL. 

[agigliotti]

could you explain what is your actual result and the expected one?

[Saro_2306]

@agigliotti  My expected result and sample data details are available in attached excel sheet

[agigliotti]

you can use this script:

a:
LOAD [Week Num],
[Team Name],
[User Name],
[Production Units],
[Working Days]
FROM
[C:\Users\Andrea\Downloads\Example Data.xlsx]
(ooxml, embedded labels, table is dati);

b:
load *,
if( [Team Name] = Previous([Team Name]), Target+Previous(TargetACC), Target ) as TargetNEW;
load *,
if( [Team Name] = Previous([Team Name]), Target+Previous(Target), Target ) as TargetACC;
load
[Team Name],
[Week Num],
sum([Production Units])/sum([Working Days]) as Target
Resident a Group by [Team Name],[Week Num] Order by [Team Name],[Week Num];

left join(b)
LOAD [Week Num],
[Team Name],
[User Name] Resident a;

drop Tables a;

finally your target is TargetNEW.

[Saro_2306]

Dear @agigliotti  thanks for your effort. but given data is not my source data. Production units and Working days count are derived by sum() expression. I need set analysis for to get desired output and should be shown in bar chart.