topic Re: Please help into this in QlikView

Please help into this

Fri, 07 Nov 2014 15:50:50 GMT

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

Re: Please help into this

avinashelite — Fri, 07 Nov 2014 15:54:15 GMT

Hi,

Try like this;

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

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

Re: Please help into this

simenkg — Fri, 07 Nov 2014 15:56:02 GMT

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

Re: Please help into this

Anonymous — Fri, 07 Nov 2014 15:56:54 GMT

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

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

Re: Please help into this

Fri, 07 Nov 2014 16:25:38 GMT

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

Re: Please help into this

Anonymous — Fri, 07 Nov 2014 21:37:56 GMT

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.

Re: Please help into this

danieloberbilli — Fri, 07 Nov 2014 21:57:52 GMT

what about

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

Re: Please help into this

MarcoWedel — Fri, 07 Nov 2014 23:14:22 GMT

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