sum of QTY Definition

[#1]

Good morning,

Dimensions:

Item ID

i have this formula/Expression:

Sum({$<Datum_fakture = {">$(=AddYears(vDatum,-1))"}>} QTY)

Legend:

Datum_fakture: InvoiceDate

vDatum: Variable where i insert the Date i want

Qty: sold Quantity

What i want:

I want the formula to summarize the sold Quantity throw the past 12 months. It should sum only the QTY from Invoices, 1 year ago till now(vDatum).

The data is not linked at all, everything is in one table(InvoiceDate, ItemID, QTY...)

I believe i did something wrong in the formula because with this one id does not summarize the asked query.

I hope somebody can help me.

Thank you.

Kristian

[#2]

Hi Kristian,

Try below expression:

sum({<[Datum_fakture]={">=$(vPrevYearDate) <=$(vCurrDate)"}>}QTY)

Values of Variables as:

vPrevYearDate=date(AddMonths(vCurrDate,-12)-1)

vCurrDate=date(max([Datum_fakture]))

I hope this will solve ur purpose.

Regards

Pratibha

[jonathandienst]

Hi

Your expression looks OK to me. I expect the problem is to do with date formats. Are Datum_fackture and vDatum in the default date format for the model?

Try replacing vDatum with an explicit date and check whether the expression works:

... AddYears('2012/06/19', -1) ...  (use correct date format for your model)

If it does, the problem could be the format of vDatum. You could try

... AddYears(Date#(vDatum, 'YYYY-MM-DD')) ...   (use format code to match vDatum)

If Datum_fakture is not in the deafult date format, then wrap the AddYears with a Date(..., 'format for Datum_fakture')

Hope that helps

Jonathan

[#4]

Thank you for your help. I found the problem, i didnt refresh my databse. The formula was fine, but it took old Data...

Greetings, Kristian