Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.