topic Comparing Dates in QlikView

Comparing Dates

Tue, 27 Jul 2010 15:23:24 GMT

I have a request from a customer to be able to compare sales data from a given time period. They want to be able to compare any particular date chosen from 2 different list boxes. What I have done is put the data in 2 different tables and added 2 seperate list boxes to the sheet. I have loaded 2 different date fields into the document. Although they contain the same values the field names are different. How do I get 1 table to display the values chosen from 1 listbox and the other table to display the values chosen from the second list box?

Comparing Dates

Tue, 27 Jul 2010 16:47:28 GMT

The Date fields should have the same name

Comparing Dates

Tue, 27 Jul 2010 17:00:36 GMT

Then how do I specify which table relates to the specific date chosen in the list box?

Comparing Dates

Tue, 27 Jul 2010 17:02:19 GMT

For example....

I want Table1 to show data related to June 2009 which would be chosen in ListBox1. Then I want Table2 to show data related to June 2010 which would be chosen in ListBox2.

Comparing Dates

Tue, 27 Jul 2010 17:13:39 GMT

To compare different periods you can use Set Analysis.

For your example:

1. pick a June month from your calendar

2. first expression for 2010 is sum({$ <Year={"2010"}>} Value)

3. second expression for 2009 is sum({$ <Year={"2009"}>} Value)

Comparing Dates

Tue, 27 Jul 2010 18:53:16 GMT

Thanks I believe that is what I am looking for. I will try it out and let yopu know. Thanks again.

Comparing Dates

Tue, 27 Jul 2010 21:31:17 GMT

I think I am doing something wrong. I have added the expression to the pivot tables, but my problem is that when I choose 2009 in one list box the other list box value changes also. How can I make it to where the data is pretty much unrelated. No matter what I do it's like whatever date I select for either list box everything on the sheet changes to that specific date. What am I doing wrong??? I apologize and I know this is probably pretty basic, but I am fairly new to QlikView.

Comparing Dates

Tue, 27 Jul 2010 23:37:21 GMT

in the development environment, click on the table viewer icon. (or, go to the 'File' menu, and select 'Table Viewer') it will show you how your tables are related. You can use it to identify the field that is causing the join, and then you can modify your script to prevent it.

On a somewhat related note, it sounds like you're going about satisfying your requirement in a very inefficient way. you could accomplish what you're attempting through the use of variables, set analysis, and a single set of related tables.

Comparing Dates

Thu, 29 Jul 2010 19:24:16 GMT

My data is coming from only 1 Table in the database and so there are no joins. I have a small sample application created to show what I want to do. Is there a way to send that or attach it to the thread???

Comparing Dates

Thu, 29 Jul 2010 19:41:44 GMT

In the posting window (not the quick post, but the full reply, or edit window) click on the options tab at the tob, and you can attach your file there.

Comparing Dates

Thu, 29 Jul 2010 20:53:48 GMT

Not sure if I need to post the excel spreadsheet or nit, but if I do let me know.

Comparing Dates

Fri, 30 Jul 2010 14:16:12 GMT

Something like this

Comparing Dates

Fri, 30 Jul 2010 16:08:13 GMT

Nick, Thanks for responding, but that doesn't fix my problem. I was able to get the expression to work that you gave me. The problem is the user wants to see 2 grids on the same sheet at once and compare the data in both grids relative to what is selected for the given list boxes. The sample that you sent me only has 1 set of list boxes and 1 grid. Thanks

Comparing Dates

Fri, 30 Jul 2010 16:29:05 GMT

I've showed to you an approach, layout is your responsibility.

Clone the table, remove last two expressions from original table and first two expressions from cloned table.

Comparing Dates

Fri, 30 Jul 2010 19:47:06 GMT

One of us is obviously confused about what is trying to be accomplished and how to do this. Here is a detailed example.

I have 4 List boxes and 2 pivot tables on 1 sheet.

2 list boxes will contain years(Call these Year1 & Year2) the other 2 listboxes will contain Months(Jan - Dec)(Call these Month1 & Month2)

2 pivot tables(Call these Table1 & Table2) will have the same layout and fields.

There may be 10 years of data or there may only be 2 years of data that is loaded from 1 database table. The database table contains a date field(TransactionDate). The user wants to be able to select a year and month from the first set of listboxes(Year1 & Month1) and then 1 pivot table(Table1) would show the data from that period selected.

Now we move on to the second set of listboxes and pivot table. The user wants to be able to select a year and month from second set of listboxes(Year2 & Month2) and then the second pivot table(Table2) would show the data from the period selected. All while the first set of listboxes and pivot table would stay the same. That way the user can compare the data between the 2.

I understand if I clone the table and add the expressions that would work, but the expression will only show data either from 2009 or 2010 and I need it to show the data from a time period that the user chooses. That is my problem.

How do I make it so that the user can choose from a set of listboxes or some other selection criteria the time frame they want to compare? Not a static time comparison. They want to be able to dynamically choose any 2 dates and do a comparison. They may want to compare 01/2010 to 03/2010 or 12/2009 to 01/2010 or 06/2009 to 06/2010 so on and so fourth.

Thanks in advance

Comparing Dates

Fri, 30 Jul 2010 20:39:57 GMT

See attachment. As I told you, the idea is the same, the implementation is up to you. You can replace sliders by calendars objects but the expressions will stay the same. If you change a structure you will need to change Set Expressions only. And the idea will remain the same.

Comparing Dates

Mon, 02 Aug 2010 15:15:41 GMT

Thanks Nick. That is exactly what I was looking for and I think I like the sliders better than the listboxes. Thanks again.

Comparing Dates

Sun, 08 Aug 2010 22:20:34 GMT

I created an app that allow user to select any two years and then show the values(Quantity) with change and % change

I set up variables to keep track of what the user selects then Set Analysis for the expressions.

This allows the user to select any 2 years and it will compare the two.

Variables:

VLowYear = if(getselectedcount([GL PERIOD YEAR])=0,max([GL PERIOD YEAR])-1,left(getfieldselections([GL PERIOD YEAR]),4))

VhighYear = if(getselectedcount([GL PERIOD YEAR])=0,max([GL PERIOD YEAR]),right(getfieldselections([GL PERIOD YEAR]),4))

VCurrPeriod = right(Max([GL PERIOD]),2)

VhighYearDay = max([MOVEMENT DATE#])

VlowYearDay = MakeDate( VlowYear, VCurrPeriod,Day(max([MOVEMENT DATE#])))

Expression in Chart:

Label =VlowYear

Expression SUM({<[MOVEMENT DATE#] = {'<=$(VhighYearDay)'} >} [QUANTITY])

Label =VHighYear

Expression SUM({<[MOVEMENT DATE#] = {'<=$(VlowYearDay)'} >} [QUANTITY])

Label Quantity Change

Expression (column(2)-Column(1))

Label % change

Expression (column(2)-Column(1))/Column(1)

Let me know if this makes sense - if not we can hook up on a webex and I will be happy to show the app