Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

newqlikie
Contributor

Problem with Set Analysis with a period of time

Hello together,

following problem occured:

I have a database with approx.15.000.000 rows in which a turnover is included.

Now I try to compare two years.

The database begins in 02.01.2009 and ends yesterday.

To get the sum of the turnover (Umsatz) of the actual year (Jahr) is quite simple:

Sum({$<Jahr={$(=Only(Jahr))}>}Umsatz)

But to get the turnover of the last year in the same period of time drives me nuts ;-)

I tried it with following function which is not working, because it pics only the actual month and is not starting at 01. January till yesterday one year ago.

Sum({$<Jahr={$(=Only(Jahr)-1)},MONAT={$(=month(JARECDAT))}>}Umsatz))

Does anyone has an idea how to do that?

JARECDAT is always a day before the actual date (=yesterday)

MONAT means month

Thany you very much for your help.


1 Solution

Accepted Solutions
Not applicable

Re: Problem with Set Analysis with a period of time

I forgot that we shouldn't take $ but 1 for the whole dataset  -- if you click only 1 year the previous year is not included into {$}.

I recreated the data model and tested.

This one works.

Sum({1< Jahr={$(=max(Jahr)-1)}, Monat={"<$(=Month(max(JARECDAT))+1)"}>} Umsatz)

UPDATE

Here is the load script

LOAD

    JARECDAT,

    Year(JARECDAT) as Jahr,

    month(JARECDAT) as Monat,

    1 as Umsatz;

LOAD

    Today()-RecNo() as JARECDAT

    AutoGenerate (1000);

Message was edited by: dgudkov

15 Replies
Not applicable

Problem with Set Analysis with a period of time

Hi,

I´m working with these expression too, but i don´t need the second parameter

sum({<jahr={$(#=only(jahr)-1)>}umsatz)

This expression returns all your date selections except year = year -1

newqlikie
Contributor

Re: Problem with Set Analysis with a period of time

Yes, you are right. The problem I have is, that I have only selected the Year.

The actual date of the year in my database is 30.08.2011

The last date for the compare is in that case 30.12.2010 and not 30.08.2010.

The formula should look for the last date in the database (yesterday it was 30.08.2011) and should bring me the sum of the turnover in the same period of time one year earlier.

Hope it became a bit clearer.

Not applicable

Re: Problem with Set Analysis with a period of time

starting at 01. January till yesterday one year ago.

Sum({$<Jahr={$(=Only(Jahr)-1)},MONAT={"<=$(=month(JARECDAT))"}>} Umsatz)

newqlikie
Contributor

Problem with Set Analysis with a period of time

Thank you dgudkov,

but I see I have a little mistake in my first question.

JARECDAT contains all dates, starting with 02.01.2009. The last date [max(JARECDAT)] is the date of yesterday.

So your function is in my case not working, too.

Not applicable

Problem with Set Analysis with a period of time

What is the format of MONAT field?

newqlikie
Contributor

Problem with Set Analysis with a period of time

These are the settings of the script:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

and this is the "date"-part of the associated load-script:

    year(JARECDAT) as Jahr,

    month(JARECDAT) as Monat,

    day(JARECDAT) as Tag,

    monthname(JARECDAT) as JahrMonat,

    quartername(JARECDAT) as JahrQuartal,

    ceil(month(JARECDAT)/3) as Quartal,

    ceil(month(JARECDAT)/6) as Halbjahr

Not applicable

Problem with Set Analysis with a period of time

Hmm ... I don't see reasons why my formula shouldn't work. Make sure you put field names in right case -- e.g. Monat instead of MONAT. QlikView may not indicate error but silently behave incorrectly because of this.

newqlikie
Contributor

Re: Problem with Set Analysis with a period of time

The field names are in the right case.

The problem is, that the last date of the year 2011 for today is 30.08.2011

But the last date of the year 2010 is 30.12.2010. This ist the date, qv is using.

The formula "month(JARECDAT)" is delivering all month of a year (Jan to Dec).

What I might can do is to add my script like following:

    year(JARECDAT) as Jahr,

    month(JARECDAT) as Monat,

    day(JARECDAT) as Tag,

    monthname(JARECDAT) as JahrMonat,

    quartername(JARECDAT) as JahrQuartal,

    ceil(month(JARECDAT)/3) as Quartal,

    ceil(month(JARECDAT)/6) as Halbjahr,

    max(month(JARECDAT) as AktuellerMonat //actual month

and  channge following in your script:

Sum({$<Jahr={$(=Only(Jahr)-1)},MONAT={"<=$(=AktuellerMonat)"}>} Umsatz)

or do I get only the turnover of August now?

Not applicable

Problem with Set Analysis with a period of time

Oh, I see ...

then why not to get month(max(JARECDAT))? It should deliver 8.

Community Browser