Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dimension Problem. Aggr? Dunno

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.Captura.JPG

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.

Captura.JPG

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

1 Solution

Accepted Solutions
jedgson
Creator
Creator

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)

View solution in original post

5 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

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
Creator
Creator

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)

Not applicable
Author

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.