topic Re: Problem with Count of Dates in QlikView

Problem with Count of Dates

Fri, 28 Mar 2014 05:28:42 GMT

Dear Community

I have a chart that contain 3 columns..

1.week--Calender Table(dimension)

2.OrderDate--Header Fact Table

3.Shippeddate-Header Fact Table

In this There is a Association between Calender and Header Fact Table.

The main thing is I want to find the Total no of "OrderDates" Per week and Total no "ShippedDates" Per week .. These two are Date Fields

I am getting Total No Of "OrderDates" Per week By using Count(OrderDate) . if i apply the same thing for delivery date

But i am not getting Total no "ShippedDates" Perweek.

So Could you please help me how to find the Total no "ShippedDates" Per week.

I want this one using set analysis but i am not getting the exact result.

Thanks&Regards

Re: Problem with Count of Dates

nagarajupinnibo — Fri, 28 Mar 2014 05:41:15 GMT

Hi Roshan,

Could you please send sample document for it.

Thanks

-Nag

Re: Problem with Count of Dates

MK_QSL — Fri, 28 Mar 2014 07:27:42 GMT

What is P1 W1 and P1 W2 etc?

Do you want only W1 and W2 ?

Re: Problem with Count of Dates

Fri, 28 Mar 2014 08:42:04 GMT

yes,i want total count for shipped date...

Re: Problem with Count of Dates

ashwanin — Fri, 28 Mar 2014 09:15:19 GMT

Hi Roshan,

you can count the same by using =count (Distinct ShippedDate) formula.

have a look.

remarks : I have created another field P.no from your data which contains P1 W1. I segregate the W1 and P1

Re: Problem with Count of Dates

Fri, 28 Mar 2014 09:20:03 GMT

You may find the solution in this excellent blog post by Henric:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Re: Problem with Count of Dates

Fri, 28 Mar 2014 09:23:34 GMT

Dear Dirk Jonker

I tried that one earlier only ,but my manager is not accepting to create temporary tables..

So i need with the help of set analysis,using P() Function..

Re: Problem with Count of Dates

Fri, 28 Mar 2014 10:12:18 GMT

Dear Ashwini,

Thank you very much,

I tried this one but in this case i getting Same number both Orderdate,and shippeddate.

actually some dates are missing. i will explain this one individually.For example

When i select 1/mar/14 to 7/mar/14 :The total orderdates are 250 -->in orderdate list box

but When i select 1/mar/14 to 7/mar/14 :The total shippeddate are 150-->in shippeddate list box

but these two fields together in one straight table the both are coming same number.I am using

For OrderDate in the expression page is=Count(Orderdate)

For Shiipeddate in the expression page is=Count(Shippeddate)

sop in this way i am doing..but i want this one is set analysys...using P() Function.

Can u please try it

Re: Re: Problem with Count of Dates

ashwanin — Fri, 28 Mar 2014 12:35:56 GMT

Hi,

I just checked and found it correct. I have updated with Order date also. Please use

=Count(Distinct Orderdate)

=Count(Distinct Shippeddate) in your expressions.