Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Problem with dates

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.

1 Solution

Accepted Solutions
Not applicable

Re: Problem with dates

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!

6 Replies
Not applicable

Problem with dates

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

SUM

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

Not applicable

Re: Problem with dates

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

Not applicable

Re: Problem with dates

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)

Not applicable

Re: Problem with dates

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

Not applicable

Re: Problem with dates

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!

Not applicable

Re: Problem with dates

thank you man.

Community Browser