count between two dates (nested aggregation) head-ache

[#1]

Hi,

I'm running into a problem with a formula on the reporting side of my report. (Not in the script).

I need to be able to see the stock present in the currently selected date period. (So between min(date) and max(date).)

This is the formula that doesn't work (nested). It should be noted that the table is not connected directly to the date table, but goes via a customer table. So I think it should be a set analyses, or an aggr of sorts, but I can't figure it out. Thanks!!!

Count ( IF(([STORE_OUT_DATE]>= min(Date) AND [STORE_IN_DATE] < max(Date)) , StockObjectNr))

[Miguel_Angel_Baeyens · Accepted solution]

Hello Mark,

First, make sure that STORE_OUT_DATE and STORE_IN_DATE are in the same format Date is (don't know if Date is a variable or a field).

Anyway, my guess is that you are comparing a date field (STORE_OUT_DATE for example) to a numeric value (as it's returned by Max(Date)). So your final expression should work as the following

Count({< [STORE_OUT_DATE] = {'>=$(=Min(Date))'}, [STORE_IN_DATE] = {'<$(=Max(Date))'} >} StockObjectNr)

Using set analysis your chart will likely perform better.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Many thanks Miguel! This fixed my issue. A most helpful answer! Thanks!