Line Chart Issues

[#1]

Hello. I'm VERY new to QlikView and need to create a bunch of stuff but what I'm currently stuck on is needing to create a line chart where the x-axis will display date and the y-axis will display unique count of people. Here's my issue:

1) The chart needs to count distinct people and plot the total distinct count of people by month.

2) The raw date format is currently MM/DD/YYYY hh:mm:ss AM/PM and I want it to display as Jan-2014 and group the unique count of people in each month (right now it is grouped by month, day, year, hour, minute and second)

In the line chart I included these expressions:

count( DISTINCT [people_field])

date([date_field],'MM-YYYY')

The actual formulas seem to work if I create them in text boxes but not working in a line chart. My two questions:

1) How do I count distinct people and

2) How do I change the associated date to be just month and year and aggregate all the people within a month so it plots correctly?

Can you please help me get the chart properties right?

Thanks in advance:)

[JonnyPoole]

It would be handy to create some Year and Month buckets in the load script as follows:

for example

change:

load

     Date,

     otherfields,

     ...

from <source>

to

load

     Date,

     Year(Date) as Year,

     Month(Date) as Month,

     otherfields,

     ...

from <source>

Then in your line chart, add Month as a DIMENSION , remove your existing month expression but keep your customer expression (it looks OK).

OR you could just create a calculated dimension with the syntax    Month(Date)

But better to add in the load script as Year and Month are very common buckets/dimensions to use.

[jduenyas]

Hey Coke is better...

I hope you understood what Jonathan Poole wrote (because it is correct) but watch out that Month(Date) Returns a string value of the month although it can be formatted as a number.

[JonnyPoole]

great point... monthname() function would be better. 

or a mix of both  ....   

Num(Month(Date)) & '/' & year(Date) as MonthYear,

[#5]

Thank you Jonathan!

[#6]

Josh - if I didn't need your help so much I might have to respond to the Coke comment Since I am at your mercy and anyone else willing to help me, I will just say thank you so much for your fast response and sharing your QV intelligence. I really appreciate it. I will set up the date tables as you and Jonathan have suggested.

[jduenyas]

Actually I prefer Pepsi (or is it Dos XX ?)  but had to poke some fun to the seriousness of the blog.

[#8]

I knew I liked you!