Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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.

Untitled.png

2 Replies
Anonymous
Not applicable
Author

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.