topic Re: Count on Date problem in QlikView

Count on Date problem

Thu, 26 Mar 2015 13:17:11 GMT

Hi friends,

I am stuck in the issue when i need to count on date values, and i am getting 0 instead of correct numbers.

I have table like this

Week	Age
2/4/2015	0-30
2/4/2015	0-30
2/4/2015	31-60
2/4/2015	61-90
2/4/2015	90+
2/11/2015	0-30
2/11/2015	31-60
2/11/2015	31-60
2/11/2015	61-90
2/11/2015	90+
3/11/2015	0-30
3/11/2015	31-60
3/11/2015	61-90
3/11/2015	61-90
3/11/2015	90+

I need a pivot table like this

Age	2/4/2015	2/11/2015	3/11/2015
0-30	2	1	1
31-60	1	2	1
61-90	1	1	2
90+	1	1	1

For this,

i was taking one dimension Age and conditions on expressions like

Count({<[Week] = {'2/4/2015'}>}[Week])

which is returning 0.

Can you please help me on this?

Regards,

Kuldeep

Re: Count on Date problem

MK_QSL — Thu, 26 Mar 2015 13:19:22 GMT

Create a Pivot Table

Dimension

Age

Week

Expression

COUNT(Week)

Re: Count on Date problem

Thu, 26 Mar 2015 13:24:49 GMT

Hi,

Count(Week) gives

Age	count(Week)
0-30	4
31-60	4
61-90	4
90+	3

But i want the count on individual dates

Regards,

Kuldeep

Re: Count on Date problem

MK_QSL — Thu, 26 Mar 2015 13:25:45 GMT

You haven't added Week as 2nd Dimension in Pivot Table

Re: Count on Date problem

Thu, 26 Mar 2015 13:35:08 GMT

After adding the second dimension as week,

i get following result

Re: Count on Date problem

Thu, 26 Mar 2015 13:47:12 GMT

Dates got changed to some different format here

Re: Count on Date problem

Thu, 26 Mar 2015 14:05:30 GMT

Hi,

You can keep like this.

In Dimension:

Age

Week

In Expression:

COUNT(Week)

then,

You can keep hold the Week field and drag right side.

leave it.

Re: Count on Date problem

MK_QSL — Thu, 26 Mar 2015 14:21:55 GMT

Kuldeep, the week format you are showing is different from what you have given in your question...

Re: Count on Date problem

Thu, 26 Mar 2015 15:22:17 GMT

Hi Krishnama,

Thanks for your reply.

I tried doing that but was not able to get the desired result.

Regards,

Kuldeep

Re: Count on Date problem

Thu, 26 Mar 2015 15:25:03 GMT

Hey

Thanks Manish. I was using the same format in both table and qlikview file.

I don't know why the format got change while loading the data in qlikview.

But for now i am good, as i changed the date source into text string. So it works fine.

Regards,

Kuldeep

Re: Count on Date problem

awhitfield — Thu, 26 Mar 2015 15:30:40 GMT

Example attached

andy

Re: Count on Date problem

Fri, 27 Mar 2015 10:29:24 GMT

Hey Andrew,

This is perfecto!

Thank you so much

Regards,

Kuldeep