topic Re: Line Chart Issues in QlikView

Line Chart Issues

Thu, 13 Nov 2014 18:10:07 GMT

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:)

Re: Line Chart Issues

JonnyPoole — Thu, 13 Nov 2014 19:24:06 GMT

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>

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.

Re: Line Chart Issues

jduenyas — Thu, 13 Nov 2014 20:28:50 GMT

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.

Re: Line Chart Issues

JonnyPoole — Thu, 13 Nov 2014 20:38:27 GMT

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

or a mix of both ....

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

Re: Line Chart Issues

Thu, 13 Nov 2014 20:38:45 GMT

Thank you Jonathan!

Re: Line Chart Issues

Thu, 13 Nov 2014 20:43:46 GMT

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.

Re: Line Chart Issues

jduenyas — Thu, 13 Nov 2014 23:22:37 GMT

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

Re: Line Chart Issues

Fri, 14 Nov 2014 17:58:13 GMT

I knew I liked you!