Sum a value based on another another field value

[Sam_Thomas]

Hi

I am looking to sum a value, based on whether or not another field meets a condition. My tables and fields are below 

Delivery Table

DeliveryID

AmountDelivered (numeric)

DateEnded (date delivery ended)

SourceCentreID as [CentreID]

Centres

CentreID

I want to sum each delivery value per centre, for the a rolling year. 

I would also like to be able to have this as a KPI and a add filter pane with all the Centre names, so whichever centre you choose, it'll return the rolling yearly amount of tonnes delivered. 

I have tried sum(if(DateEnded>=today()-365, AmountDelivered)) which seems to work but just gives wild figures when a selection is made on the filter pane. I don't know whether it's a data modelling / joining error or an expressional error. 

Any advice will be gratefully appreciated. 

EDIT: Just to add, the SourceCentre field could be blank (delivery product is sourced from elsewhere, not a centre). I obviously need to only sum the delivery amounts for each individual centre. 

[luizcdepaula]

Hi Sam,

Refrain from using IF statements on the visualization side. Create a flag in the load script and create the logic just using set analysis. 

load script: If(DateEnded>=AddYears(today(),-1),1,0) as LastYearFlag

Visuliaztion: Sum({<LastYearFlag={1}>}AmountDelivered)

I hope it works.

Cheers,

Luiz

[MendyS]

hi 

if I got you,

It seems like a simple filter in set analysis, or if the period is fixed, you can do that in ETL 

can you send an image of your result?

[Sam_Thomas]

Hi Luiz, 

Thank you for your response. That's really interesting, I'm new to the loadscript world and still learning - but that makes a lot of sense. I wish I knew this last month!

I will try it out now, thanks.