topic Re: compare data with month from previous year in QlikView

compare data with month from previous year

Mon, 28 Apr 2014 07:59:43 GMT

Hello, I'm relatively new at Qlikview and wanted to compare some data from a selected year with its previous year.

In Qlikview I have a table in which i show the count of all the contacts there have been with the customer per mount [=count(contactsID)] with the dimension [contacts.month]

I want to show an other expression with the contacts with customers in the same month a year before.

So if my table shows all the contacts from jan-2014 until dec-2014 I want a bar next to every month with the months jan-2013 until dec-2013

for example jan-2013/jan-2014; feb-2013/feb-2014; etc

If anybody could help i would be very grateful! Thanks to all of you in advance!

Re: compare data with month from previous year

Mon, 28 Apr 2014 08:01:51 GMT

In this chart you have also year as dimension, or maybe your contacts.month field has year-month information?

Re: compare data with month from previous year

Mon, 28 Apr 2014 08:14:44 GMT

sorry for not being clear about that!

In the database i have a date field and in Qlikview i load that as

Load

date as contact.date

year(date) as contact.year,

month(date) as contact.month,

day(date) as contact.day,

so contact.month is the month of the date in the database.

Re: Re: compare data with month from previous year

Mon, 28 Apr 2014 08:31:34 GMT

TOm,

but as you told, you have selected year (i expect, one selected year).

So, your expression:

=count(contactsID)

gives you result for selected year and for months from your chart dimension.

Now if you would like to have also expression for year-1 you may use set analysis.

Find attached example.

regards

Darek

Re: compare data with month from previous year

tresB — Mon, 28 Apr 2014 08:35:49 GMT

If you are using time as dimension, two ways I can see:

Script: Create a monthly aggregated field for last year using previous/peek function on ordered data
Front end: Using a complex logic with synthetic dimension (ValueLoop()/ValueLis()) and expression using

Pick(Match(

The suggested one is script-way.

Re: compare data with month from previous year

Mon, 28 Apr 2014 08:43:47 GMT

In qlikview i made a selection option for years. If you select 2014 i want the table to show 2014 and 2013. The table has the dimension {Contact.Month} and the expresion {count(ContactID)} as you can see below

Re: compare data with month from previous year

Mon, 28 Apr 2014 08:45:41 GMT

ps. i have only a personal version. so i can't open your file. Sorry for that :$

Re: compare data with month from previous year

Mon, 28 Apr 2014 08:50:44 GMT

Add expression as:

count({<contact.year={$(=contact.year-1)}>}ContactID

regards

Darek

Re: compare data with month from previous year

Mon, 28 Apr 2014 08:53:55 GMT

Thank you very much!!!

Re: compare data with month from previous year

MWiciok — Mon, 28 Apr 2014 08:59:48 GMT

Hi,

you can use two expressions in your diagramm:

count(contactsID)
count({$ <contact.year={$(=only(contact.year) - 1)}>} contactsID)

The second expression uses set-analysis (the part in the {}-brackets).

Re: compare data with month from previous year

Mon, 28 Apr 2014 10:31:03 GMT

Thank you! i did know that i could use more expressions. Didn't knew how to create the 2nd one. But now i do!