topic Re: Dual total in YTD in QlikView

Dual total in YTD

Saro_2306 — Mon, 22 Feb 2021 12:36:11 GMT

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.

Re: Dual total in YTD

agigliotti — Mon, 22 Feb 2021 14:06:27 GMT

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

Re: Dual total in YTD

Saro_2306 — Mon, 22 Feb 2021 14:11:52 GMT

@agigliotti Not working

Re: Dual total in YTD

agigliotti — Mon, 22 Feb 2021 14:42:00 GMT

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] )

Re: Dual total in YTD

Saro_2306 — Mon, 22 Feb 2021 17:47:31 GMT

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

Re: Dual total in YTD

agigliotti — Tue, 23 Feb 2021 08:21:15 GMT

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

Re: Dual total in YTD

Saro_2306 — Tue, 23 Feb 2021 08:48:24 GMT

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

Re: Dual total in YTD

agigliotti — Tue, 23 Feb 2021 15:11:12 GMT

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.

Re: Dual total in YTD

Saro_2306 — Wed, 24 Feb 2021 09:48:53 GMT

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.