Dimension Problem. Aggr? Dunno

[#1]

Hi everybody, I have a Straight Table with several dimensions and one expression (Sum(Amount)). This table shows the Invoices, Credit Notes and Payments of my suppliers.

When a Invoice has been closed by a Credit NOte, a Payment or both, the different lines have a common field value ([Nº Mov]) as you can see in the picture.

There an Invoice in red and a Credit NOte plus a Payment in green. Well what I'd like to get, is to change the MOv. Dimension into a Calculated dimension based on the date and the type of the Document. I have two Date Selector and I want that the invoices Date rules.

I want a dimension like that

If (("Document Date" is between this two dates variables)) AND (Document Type=Invoice), "Mov") so showing only the Non null values I`d get it.

Any idea?

Thanks

[swuehl]

Maybe like

if( Fecha >= vFechaInicial and Fecha <= vFechaFinal and [Document Type]='Invoice', Mov)

as calculated dimension? I assumed vFechaInicial and vFechaFinal are variables you set in your calendar object. Fecha is your date field and [Document Type] your document type field. Mov field is called [N° Mov] instead?

If you have only one expression (and it will also work with more then one), you could also try this as expression instead of the calc. dimension:

sum({<Fecha = {">=$(vFechaInicial)<=$(vFechaFinal)"}, [Document Type] = {'Invoice'}>} Amount)

using set analysis, which should perform better.

Hope this helps,

Stefan

[#3]

I think I can`t use it int the expression because I want to show all the lines involved (Invoices+Credit Notes+Payments), but I think I`m cllose to  to the solution thanks to you, see:

=  IF((CalendarDate<=(Date(vFFF))) AND(CalendarDate>=(Date(vFIF)) AND
([VLE.Tipo Documento]='Invoice')),
[VLE. Nº Mov],IF([VLE. Nº Mov]=[VLE. Nº Mov],[VLE. Nº Mov]))

The thing is:

If you are an Invoice, let me see all the invoices into the range, 
but if your are not an invoice let me see all the lines
with the common "Mov" Filed value of those invoices lines,
 whatever your Date.

[swuehl]

What do you want to achieve with this else part of your calculated dimension if statement:

IF([VLE. Nº Mov]=[VLE. Nº Mov],[VLE. Nº Mov])

I believe that this is equivalent to just [VLE. N° Mov]

If I understood your last requirement line correctly I would try:

=  IF((CalendarDate<=(Date(vFFF))) AND(CalendarDate>=(Date(vFIF)) AND ([VLE.Tipo Documento]='Invoice')),[VLE. Nº Mov],IF([VLE.Tipo Documento] <>'Invoice',[VLE. Nº Mov]))

[jedgson · Accepted solution]

Are you trying to display all invoices that fall between the selected date range and all credit notes/payments regardless of the date but only those credit notes/payments that have the same [VLE. Nº Mov] as an invoice that falls within the date range?

If so you are best to do this in the expression with something along the lines of:

sum({< [VLE. Nº Mov] = P({<[VLE.Tipo Documento] = {'Invoice'}, [CalendarDate] = {'>=$(=date(vFFF))<=$(=date(vFIF))'}>} [VLE. Nº Mov]) >} Amount)

[#6]

Thanks a lot !! It works. In the meantime I solved in another way. Mapping this while loading the table:

Date(If("Document Type" <>'2', ApplyMap('MapFirstDateDocumentVendor',"Vendor Ledger Entry No_"),

"Posting Date"),'DD/MM/YYYY') AS FirstDocumentDate;

And then filtering by the FirstDocumentDate, the Date of the original Document, the invoice. So every line will have 2 dates, its real date and this FirstDocumentDate

Thank you both for your time.