topic Re: Help making FinanceDate in QlikView

Help making FinanceDate

Fri, 20 Feb 2015 16:59:13 GMT

Hi all

I have some trouble getting the right FinanceDate to invoices

I have invoices with 2 dates

- InvoiceDate

- InsertDate (the date the invoice has been inserted)

I have a table with date for FinanceMonthCloseDate with

- FinanceMonth

- FinanceMonthCloseDate

What I want is to get a FinanceDate, that matches the FinanceMonth

If Invoice Month = Insert Month then InsertDate

if Invoice Month < Insert Month and InsertDate <= FinanceMonthCloseDate then InvoiceDate

But if FinanceMonthCloseDate is in a FinanceMonth that have another FinanceMonthCloseDate than Insertdate, then the FinanceDate should be 01 in FinanceCloseMonth that match the Insertdate(Month)

Attached file with sample data

My problem is the Invoice in file with "yellow" (no 10), when I make the FinanceDate I can't get it right, the result I would like is 29-09-2014 (InsertDate)

Hope the question made sence

Regards

Meddelelse blev redigeret af: Niels Juel Attached qvw file with sample

Re: Help making FinanceDate

Fri, 20 Feb 2015 18:38:33 GMT

Hi,

could you please attached an example?

Re: Help making FinanceDate

Fri, 20 Feb 2015 21:13:43 GMT

Example attached

Re: Help making FinanceDate

Fri, 20 Feb 2015 21:23:19 GMT

you should try your condition in pivot table

Re: Help making FinanceDate

Fri, 20 Feb 2015 21:27:29 GMT

why should I do that ?

The FinanceDate is to be used with other FinanceDates in the full QV as the Date dimension. This is just an example of what I need in this case.