Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Monthly evolution of Weighted Payment Days

Hello all,

I am stuck with this problem I would like to share. The goal is to make a chart / line chart showing the monthly evolution of the Weighted Payment Days for a selection of customers.

Our Customer table has these fields:

Customer
CustomerID
InvoiceID
ClosingDate -
PaymentDays (the number of days until the invoice was paid)
Amount

The goal is a chart like this (it can later be converted to a line chart I suppose).

MonthWeighted Payment Days
Apr 201665
Mar 201667,6
Feb 201666,7
Jan 201670
......

I have tried this way:

  1.      I have created a variable named vClosingDate.
  2.      The calculation for the Weighted Payment Days is as follows:


=

sum({ < [ClosingDate] = {">=$(=date(AddMonths($(vClosingDate),-12),'DD/MM/YYYY')) <=$(=date($(vClosingDate),'DD/MM/YYYY'))"}  > }  [PaymentDays* Amount])

  /

sum({ < [ClosingDate] = {">=$(=date(AddMonths($(vClosingDate),-12),'DD/MM/YYYY')) <=$(=date($(vClosingDate),'DD/MM/YYYY'))"},  > }  [Amount])

It takes into account all those invoices closed 12 months back from the selected ClosingDate, and does the division of sum ([PaymentDays* Amount]) / sum [Amount]

I am using the vClosingDate as the dimension for the chart, but this way I get a correct result, but only for the date selected, something like this:

monthname(vClosingDate)Weighted Payment Days
Apr 201665

But no more rows are available using this approach. Is there any way I could get the rest of time periods?

Any help will be highly appreciated!

5 Replies
Highlighted

Re: Monthly evolution of Weighted Payment Days

Would you be able to share a sample as it would be easy to test out rather than making random guesses

Highlighted

Re: Monthly evolution of Weighted Payment Days

What is your vClosingDate Definition?  Sample would be easier to give suggestion.

1st Guess :  May be try Month([Closing Date]) as Dimension

Highlighted
Specialist II
Specialist II

Re: Monthly evolution of Weighted Payment Days

Hello,

there goes a sample. Please let me know if you need any clarification.

Best regards.

Highlighted

Re: Monthly evolution of Weighted Payment Days

I am not able to open this

Highlighted
Specialist II
Specialist II

Re: Monthly evolution of Weighted Payment Days

Sorry, please check this one.