topic Include 0 value dates between min and max days in App Development

Include 0 value dates between min and max days

Fri, 10 Feb 2017 20:48:40 GMT

We are using Qlik Sense Server version 2.

I am looking to include all 0 value dates on a bar chart between the min and max day of something occurring. I will try to explain the situation as best as I can.

We have a fact table listing out all open quotes, open orders, and invoices. We identify whether it is on of those in a field called SalesTransactionType. Each type of transaction has a date key that connects to its designated date dimension table with the various dates, years, months, weeks, etc. in that table.

The issue I am having is that we want to see a bar chart with all days, including days with no values, but set dynamically between the first occurrence of an Open Order and the last occurrence. The following expression gives me the first part, with all dates with zero, but does not limit the range:

sum ({$< SalesTransactionType = {'Open Order'}>} SalesTopLine)

sum({$}0)

Our date dimension table includes all dates from 2012-2017. So our bar chart goes from 2012-2017. What I want to be able to do is shrink that bar chart down dynamically to only the first date of an Open Order in our table through the last date of an Open Order in our table. The two tables are kind of structured like this:

SalesTransactionType	QuoteDate	OrderDate	InvoiceDate	SalesTopLine
Open Quote	12-01-16			10.23
Open Order		12-02-16		13.55
Invoice			1-13-17	152.65
Open Order		1-22-17		22.75

OrderDate	Year	Month	YearMonth	Day
12-02-16	2016	12	2016-Dec	2
12-03-16	2016	12	2016-Dec	3

Does anyone have a suggestion on how to do this?

Thanks y'all!

Re: Include 0 value dates between min and max days

rubenmarin — Sat, 11 Feb 2017 11:50:25 GMT

Hi David, try with:

sum ({$<OrderDate={">=$(=date(Min({<SalesTransactionType = {'Open Order'}>} OrderDate)))<=$(=date(Max({<SalesTransactionType = {'Open Order'}>} OrderDate)))"}>} SalesTopLine)

Set analysis applied to the field used as dimension, and retrieving the min and max values for that field with SalesTransactionType='Open Order'.

Hope this helps.

Re: Include 0 value dates between min and max days

Sun, 12 Feb 2017 22:26:18 GMT

So close, but you totally got me there. Thank you so much. I ended up with this expression:

sum ({$<SalesTransactionType = {'Open Order'}, OrderRequiredDateKey={">=$(=date(Min({<SalesTransactionType = {'Open Order'}>} OrderRequiredDateKey)))<=$(=date(Max({<SalesTransactionType = {'Open Order'}>} OrderRequiredDateKey)))"}>} SalesTopLine)

+ sum({$<OrderRequiredDateKey={">=$(=date(Min({<SalesTransactionType = {'Open Order'}>} OrderRequiredDateKey)))<=$(=date(Max({<SalesTransactionType = {'Open Order'}>} OrderRequiredDateKey)))"}>}0)

It appears to be working perfectly. Thank you again!

Aloha,

Dave