Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:)
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.
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.
great point... monthname() function would be better.
or a mix of both ....
Num(Month(Date)) & '/' & year(Date) as MonthYear,
Thank you Jonathan!
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.
Actually I prefer Pepsi (or is it Dos XX ?) but had to poke some fun to the seriousness of the blog.
I knew I liked you!