Qlik Community

Ask a Question

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.

Untitled.png

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