topic Date Comparison in QlikView

Date Comparison

Thu, 25 Jun 2015 19:09:57 GMT

Hi,

Just wondering if anyone could help me with a way to present my data. I think if i changed the date column to months and put in each month manually and a year column with the year beside it then it would be grand but the file i am using would be quite large this is only a small sample.

I am looking to say use some sort of formula so i can look at any month and see who the best salesperson was for that month. I realise my example is quite small but for Example in June 2015 i would see that John sold 3 and Helen and Alan sold 1 each.

I would be looking to create a bar chart and say for Example click on any month and to see who are the top selling salespeople that month.

Here is the sample Excel file.

Seller	Company	Date
John	eee	28/03/2013
John	eee	28/03/2013
Steven	qqqq	29/04/2013
Micheal	wwww	30/04/2013
Steven	eeee	06/07/2013
Milly	rrrr	01/09/2013
Rachel	tttt	02/11/2013
Mark	yyy	03/11/2013
Rachel	uuuu	04/04/2014
Milly	iiii	05/04/2014
John	oooo	06/06/2014
john	pppp	07/07/2014
Steven	aaaas	08/07/2014
Peter	ssss	09/07/2014
Paul	dddd	10/07/2014
Mark	ffff	11/07/2014
Tom	ggghhh	12/07/2014
David	hhhhhhj	13/07/2014
alan	jjjjjj	14/07/2014
Alan	kkkkk	15/09/2014
tom	lllll	16/09/2014
David	vvvv	17/11/2014
David	bbbbb	18/12/2014
Glenn	nnnnn	01/01/2015
Roisin	mmmmmeeee	02/01/2015
Helen	dfdfssf	03/01/2015
John	gregtd	04/03/2015
Mark	gtrhuykui	05/03/2015
Helen	iukiuku	06/03/2015
Helen	kthe	07/06/2015
john	eretf	08/06/2015
john	hfthtf	09/06/2015
john	yrergd	10/06/2015
Alan	grreed	11/06/2015

Here is the sample Excel like loaded into Qlikview. As you can see i have a dimension of Salesperson and an expression of count Company so i can see who sold the most companies.

What i want to do is to be able to say be able to do this to see who is the top Seller for say March or any month.

Thanks

Re: Date Comparison

sunny_talwar — Thu, 25 Jun 2015 19:17:33 GMT

Something like this?

Re: Date Comparison

Thu, 25 Jun 2015 19:30:10 GMT

Hi Sunindia,

Thanks for the reply that is exactly what i want can you explain how you changed the date to MonthYear from the way i had the date formatted if you could.

Thanks again.

Re: Date Comparison

sunny_talwar — Thu, 25 Jun 2015 19:33:49 GMT

There is a function in QlikView called MonthName() which changes date into Month Year format

Re: Date Comparison

Thu, 25 Jun 2015 19:53:54 GMT

Hi,

I just seen the script you loaded in i pasted it below so i just paste this in when i am loading the data i presume and it changes the Date to monthyear it is that correct?

Join(Sheet1)

Add LOAD Date,

MonthName(Date) as MonthYear

Resident Sheet1;

Re: Date Comparison

sunny_talwar — Thu, 25 Jun 2015 19:55:49 GMT

No, for you it would be a little different. Since I did not have the source data file, I had to do a partial reload to add MonthName(Date) in there. You can do something much more simpler like this:

LOAD Seller,

Company,

Date,

MonthName(Date) as MonthYear

FROM

[..\..\Example.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date Comparison

Thu, 25 Jun 2015 20:02:25 GMT

That worked perfect thank you saves me a great deal of time.

Re: Date Comparison

sunny_talwar — Thu, 25 Jun 2015 20:04:11 GMT

No problem at all

I am glad I was able to help.

Best,

Sunny