# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Search instead for
Did you mean: Not applicable

## Measure from a date range and display by that date dimension

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. 2 Replies  Specialist

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) Not applicable
Author

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. 