topic count between two dates (nested aggregation) head-ache in QlikView

count between two dates (nested aggregation) head-ache

Tue, 14 Jun 2011 12:56:53 GMT

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))

Re: count between two dates (nested aggregation) head-ache

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 13:13:04 GMT

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

count between two dates (nested aggregation) head-ache

Tue, 14 Jun 2011 13:25:50 GMT

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