1 2 Previous Next 15 Replies Latest reply on Sep 19, 2017 6:45 AM by Andrea Benvenuti

Need help with calculation

Hello guys hopefully someone can help me with this calculation, I will try to be as clear as possible.

Right now I am trying to make a sum of a calculation  but before we dive there this is the situation, I have this fields:

ID_SOSPENSIONE

ID_ACCESSO

ID_PRESA_CARIC

I am calculating (trying to ..)for each  ID_PRESA_CARIC the sum of the days of Sospensione, keep in mind each ID_PRESA_CARIC might have 1 or more ID_SOSPENSIONE.

That said i am calculating each SOSPENSIONE by making the difference betweeen the access min and max for each id ID_SOSPENSIONE, so i have 3 calculated field

CALCOLO_GIORNI_SOSP1

DATEDIFF('day', IFNULL([DATA_MIN_ACCESSO], TODAY() ), IFNULL([DATA_MAX_ACCESSO], TODAY() ))

CALCOLO_GIORNI_SOSP2

IF

DATEDIFF('day', IFNULL(ATTR([DATA_FINE_SOSPENSIONE]), TODAY() ), [DATA_MAX_ACCESSO]) <= 0 THEN 0

ELSEIF DATEDIFF('day', IFNULL(ATTR([DATA_FINE_SOSPENSIONE]), TODAY() ), [DATA_MIN_ACCESSO])>= 0 THEN 0

ELSE

DATEDIFF('day', IFNULL(ATTR([DATA_FINE_SOSPENSIONE]), TODAY() ), [DATA_MAX_ACCESSO])

END

CALCOLO_GIORNI_SOSP3

IF

DATEDIFF('day', [DATA_MIN_ACCESSO], ATTR([DATA_INIZIO_SOSPENSIONE])) <= 0 THEN 0

ELSEIF DATEDIFF('day', [DATA_MAX_ACCESSO], ATTR([DATA_INIZIO_SOSPENSIONE])) >= 0 THEN 0

ELSE DATEDIFF('day', [DATA_MIN_ACCESSO], ATTR([DATA_INIZIO_SOSPENSIONE]))

END

At the end the total for each sospensione is correct (if i put in the row field ) fopr example i see id 1 as 3 days and id 2 as 4 days, but I cannot get the sum to work, if i remove the id_sospensione from the row i get an empty box instead of the sum of both value (in this case should be 7).

Hopefully it is clear enought!!

Thank you for any help!!!!!!

• 1. Re: Need help with calculation

Forgot to say that the formula I use now to sum is

IF [CALCOLO_GIORNI_SOSP2]+[CALCOLO_GIORNI_SOSP3] = 0 THEN 0

ELSE [CALCOLO_GIORNI_SOSP1]-[CALCOLO_GIORNI_SOSP2]-[CALCOLO_GIORNI_SOSP3]

END

but as said I cannot get the sum to work, if i remove the id_sospensione from the row i get an empty box instead of the sum of both value.

• 2. Re: Need help with calculation

Hi Andrea,

You're pretty close with your formula. You just need to sum you measures, like so:

IF SUM([CALCOLO_GIORNI_SOSP2]) + SUM([CALCOLO_GIORNI_SOSP3]) = 0 THEN 0

ELSE SUM([CALCOLO_GIORNI_SOSP1]) - SUM([CALCOLO_GIORNI_SOSP2]) - SUM([CALCOLO_GIORNI_SOSP3])

END

1 of 1 people found this helpful
• 3. Re: Need help with calculation

Thank you for your assistance, but unfortuantly it is not that easy

As i cannot sum an aggregated...

As you see GES (giornate_sospensione) is null, but if i add the id_sospensione i get 2 row instead of one (one for each sospensione) and the count is right.

If only I could sum it...

1 of 1 people found this helpful
• 4. Re: Need help with calculation

Hi Andrea,

Thanks for the response, I figured it wasn't that easy Is it possible to upload a packaged workbook for us to examine?

Walt

• 5. Re: Need help with calculation

Thanks, i cannot attach it here cause it is big, so I uploaded it here:

macabc.it/forumexample.zip

I already setted up everything and added the ID_Sospensione as i said on the row so you can actually see the calc.

As you can see GEC (should be ) is the calc of  all days - the sospensioni (GES) in this case the total is 364

i have 2 suspension  one of 48 days and one of 7 and correctly the gec on each sospensione is 316 (364 - 48) and the second is 357 (364 - 7).

Now everything is correct, but the total does not computer as 364 is the total days and it shows, but i cannot make the sum of the 48+7  (55)

so the grand total should be  309 (364-55) as GEC and 55 48+7 as GES.

Hope it is clear like that .

• 6. Re: Need help with calculation

Any luck with the file?

Thanks

Andrea

• 7. Re: Need help with calculation

Sorry to bother, but did anyone find a solution?

Is it even possible to do what I am trying to do?

Thanks

• 8. Re: Need help with calculation

Most people using office laptops cant download data from other places. If you can, please create some sample data and use that to create your calculations and then upload it to tableau community.

• 9. Re: Need help with calculation

Thank you for your time, since the item i uploaded is a sample date, how to i put it into a tableau community ?

Thanks

• 11. Re: Need help with calculation

Thank you for the heads up, here it is

• 12. Re: Need help with calculation

It seems you have not saved properly in .twbx format. When we opened your attached file it asks for the user name & password. Extract some sample data so that we can check.

• 13. Re: Need help with calculation

Hi Andrea,

You have posted the workbook with live connection. Please create an extract and post it.

Thanks,

Pavan

• 14. Re: Need help with calculation

Well hopefully 3rd time it's a charm  .... sorry for that!

1 2 Previous Next