Date range in set analysis not working

drjoffily — Thu, 21 Mar 2013 05:06:08 GMT

Hi, I have this issue where I'm calculating the extrapolation (please see the formula below) but for some reason it's returning ZERO for some dealers.

I've investigated it and have an idea what's going on but I don't know how to get around that.

If you check the screenshots attached you'll see Mel and David do have revenue in the YTD FY13 column but the column YTD FY13 Extrapolated is showing ZERO!

The formula to calculate the extrapolation on column YTD FY13 Extrapolated is as below:

=Sum(

Aggr(

Sum({<Month=, Year=, [Financial Year]=, [Deal Date] = {'>=$(=Date(YearStart(Min([Deal Date]), 0, 10)))<=$(=Date(Max([Deal Date])))'} >} [Daily Margin@AUD])

/ networkdays(YearStart(Max([Deal Date]), 0, 10), Max([Deal Date]))

* networkdays(YearStart(Max([Deal Date]), 0, 10), yearEnd( Max([Deal Date]), 0, 10))

, [Sales Dealer Name]

))

/1000

The reason I'm using Aggr is because this pivot table was giving me wrong totals as QV pivot tables don't sum row by row - don't ask me why! Otherwise the formula would be just from the second SUM.

So, what I found out was that if I select Mel for example (second screenshot) my calendar unselect MAR as he doesn't have revenue in March and the correct extrapolation is returned. Therefore when I'm calculating the date range in the formula above, I'm guessing my Max(Date) will be null in Mel's case as he doesn't have date in March. Thus QV is returning ZERO when Mel is not selected.

Does anyone have any idea how to fix this problem?

Thanks

topic Date range in set analysis not working in QlikView

Date range in set analysis not working