topic Re: Measure from a date range and display by that date dimension in QlikView

Measure from a date range and display by that date dimension

Mon, 14 Nov 2016 13:30:19 GMT

Hi friends,

I need to calculate all invoiced items in a dynamic time period. This period is calculated from a given date (a selected date for a KPI and the x-axis for a visualization). If the given date would be the 14th of November 2016, then I would need to receive the sum of all invoiced items between (and including) the 1st of November 2015 and the 31st of October 2016.

I was able to create a KPI with this expression perfectly when selecting a date. When selecting a number of dates, it calculates the KPI from the most recent date (Max()). In the image, this is the KPI on the right.

Sum({1<SInvoice_DateFull={">=$(=Max(MonthStart(AddMonths(DTOContact_DateFull,-12))))<=$(=Max(MonthEnd(AddMonths(DTOContact_DateFull,-1))))"}>}InvoiceQuantity)

But I need to calculate and visualize the expression above for the last 12 months, including the current month, on a bar chart (I used a pivot table for convenience).

I've tried to visualize the following expression as a measure against the DTOContact date dimension, displayed in months.

Sum({1<SInvoice_DateFull={">=$(MonthStart(AddMonths(DTOContact_DateFull,-12)))<=$(MonthEnd(AddMonths(DTOContact_DateFull,-1)))"}>}InvoiceQuantity)

The results I'm getting don't make any sense to me.

I'm quite new to Qlik and to BI in general.

Re: Measure from a date range and display by that date dimension

Anonymous — Mon, 14 Nov 2016 14:34:58 GMT

Sum({1<SInvoice_DateFull={">=$(MonthStart(AddMonths(DTOContact_DateFull,-12)))<=$(MonthEnd(AddMonths(DTOContact_DateFull,-1)))"}>}InvoiceQuantity)

I think the above expression will give sum up to 31 OCTOBER 16 ?

Do you need up to current date 11/14/2016 . ?

Sum({1<SInvoice_DateFull={">=$(MonthStart(AddMonths(DTOContact_DateFull,-12)))<=$(Max(DTOContact_DateFull))"}>}InvoiceQuantity)

or Up to Current month end ? 11/30/2016 ?

Sum({1<SInvoice_DateFull={">=$(MonthStart(AddMonths(DTOContact_DateFull,-12)))<=$(MonthEnd(Max(DTOContact_DateFull,)))"}>}InvoiceQuantity)

Re: Measure from a date range and display by that date dimension

Mon, 14 Nov 2016 14:43:35 GMT

I need the expression to sum InvoiceQuantity from the past year, up to the last complete month. So this would be from 1 November 2015 to 31 October 2016.

But in any case, the date range stays the same in both expressions. They give different results though for a given month and there lies my problem.