Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Include 0 value dates between min and max days

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:

SalesTransactionTypeQuoteDateOrderDateInvoiceDateSalesTopLine
Open Quote12-01-1610.23
Open Order12-02-1613.55
Invoice1-13-17152.65
Open Order1-22-1722.75

OrderDateYearMonthYearMonthDay
12-02-162016122016-Dec2
12-03-162016122016-Dec3

Does anyone have a suggestion on how to do this? 

Thanks y'all!

1 Solution

Accepted Solutions

Re: Include 0 value dates between min and max days

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.

2 Replies

Re: Include 0 value dates between min and max days

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.

Not applicable

Re: Include 0 value dates between min and max days

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

Community Browser