Problem with Set Analysis with a period of time

[newqlikie]

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.

[#2]

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]

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.

[#4]

starting at 01. January till yesterday one year ago.

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

[newqlikie]

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.

[#6]

What is the format of MONAT field?

[newqlikie]

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

[#8]

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]

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?

[#10]

Oh, I see ...

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