topic Re: Problem with dates in QlikView

Problem with dates

Thu, 09 Jun 2011 17:46:25 GMT

Well first of all, sorry for my english it's out of practice, second, i have a litle problem, i have a date for example 09/06/2011 and i want to sum all the sales between the fitst of january to this date and do that for every year and month that i select, for example

if i have selected june 2011 i have to obtain de sum of january 2011 to june 2011 and if i select march obtain de sum of janury 2011 to march 2011 and obtain the same for 2010.

I don´t know of i'm clear but that´s my question and i hope that some one can help me.

thanks.

Problem with dates

Thu, 09 Jun 2011 18:42:38 GMT

A set analysis statement like the one below should help you in this situation.

SUM

({$<Fiscal_Year={$(=max(year(Fiscal_Year)))}>}Sales)

Re: Problem with dates

Thu, 09 Jun 2011 19:43:01 GMT

correct me if i'm wrong but that only helps me in the current year, what if i wanth a comparation in the same period of months but for the last year

Re: Problem with dates

Thu, 09 Jun 2011 20:34:31 GMT

You can use something like the following to get previous years:

SUM

({$<Fiscal_Year={$(=max(year(Fiscal_Year))-1)}>}Sales)

Add month & date selection box's and you can compare as many different time periods as needed.

So if you select 2010 as your current Fiscal_Year, the above statement will display data for 2009. If you have no Fiscal_Year selected it will use max - 1, or 2010.

Further you can combine them in your expression to just show you the change:

SUM

({$<Fiscal_Year={$(=max(year(Fiscal_Year)))}>}Sales)

SUM ({$<Fiscal_Year={$(=max(year(Fiscal_Year))-1)}>}Sales)

Re: Problem with dates

Thu, 09 Jun 2011 20:40:05 GMT

thank you, you help me alot to clear that out. one last question if i want to do that but including de days not only the months?, i mean

09/06/2011, 09/06/2010

Re: Problem with dates

Thu, 09 Jun 2011 21:04:38 GMT

In your load script identify the day of the month somewhere in your tables (1-31) and the name of the month (Jan-Dec) then add those fields into a listbox or multi-box which the users can use to select a specific month or day to compare year over year.

Sales:

Load SalesDate,

year(SalesDate) as SalesYear,

quarter(SalesDate) as SalesQtr,

month(SalesDate) as SalesMonth,

weekday(SalesDate) as SalesDay,

day(SalesDate) as SalesDayNum,

From Sales;

Putting all of the above fields into listbox's will allow the users to compare virtually any year over year period they wish.

Then change the set statement from earlier to read

SUM ({$<SalesYear={$(=max(year(SalesYear))-1)}>}Sales)

SUM ({$<SalesYear={$(=max(year(SalesYear)))}>}Sales)

When a user selects 2009 from the SalesYear listbox, the formulas will look at 2008 & 2009.

When they then select 2 from the SalesQtr listbox the formulas will compare Q2 2008 to Q2 2009...

Hope that helps!

Re: Problem with dates

Thu, 09 Jun 2011 21:41:05 GMT

thank you man.