Please help into this

[#1]

Hello Everyone,

Can any body please help into that,

I wanted to count the dates where the fact is not null,

lets say for November i have Fact value only for 2 days,

when am using count(date) funtion it is showing 30, i need to implement the condition like count of Date where fact is not null.

i need to implement this in the expression.

Can anybody please help into that.

Thanks in advance

[avinashelite]

Hi,

Try like this;

if(fact<>null(),count(date))

or

if(len(fact)>0,count(date))

[simenkg]

Count({<fact={*}>}date)

[Anonymous]

You could try this with Set Analysis - usually more efficient:

count({$<date=P({$<fact=P(fact)>}date)>}distinct date)

[#5]

Hello Steve

it is giving total number of month,

lets say i have data for two days in November, but it is displaying 30,

=count({$<date=P({$<Revenue=P(Revenue)>}DATE)>}distinct DATE)

this is my condition,

Please help me

[Anonymous]

I'm assuming you're having this issue because you have a loosely coupled master calendar or something along those lines. Does your facts table have a date key field that you would use to map to the calendar?

If so, use that field instead of date.

If that doesn't work maybe:

=sum(if(Aggr(sum(Revenue),date)<>0,1)

Above is just a theory. I've never tried to do this.

[danieloberbilli]

what about

count({<Revenue={">0"}>} Date)

[MarcoWedel]

Hi,

attached some test application to compare the performance of different solutions.

Solutions using set expressions seem to perform better than those with if-statements:

expressions (slowest to fastest):

=Count(DISTINCT If(not IsNull(fact), Date))

=Count({$<Date=p({$<fact={*}>} Date)>} DISTINCT Date)

=Count({<fact={*}>} Distinct Date)

test data:

tabData:
LOAD *,
    Day(Date) as Day,
    WeekDay(Date) as WeekDay,
    Week(Date) as Week,
    WeekName(Date) as WeekName,
    Month(Date) as Month,
    MonthName(Date) as MonthName,
    Ceil(Month(Date)/3) as Quarter,
    QuarterName(Date) as QuarterName,
    Year(Date) as Year,
    WeekYear(Date) as WeekYear,
    If(Rand()>0.99999,Ceil(Rand()*100)) as fact;  
LOAD Date(YearStart(Today())+IterNo()-1) as Date
AutoGenerate 100000
While IterNo()-1<=DayNumberOfYear(Today());

hope this helps

regards

Marco