15 Replies Latest reply: Sep 1, 2011 2:38 AM by Riko Mahr

# 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.

• ###### 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

• ###### 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.

• ###### 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)

• ###### 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.

• ###### Problem with Set Analysis with a period of time

What is the format of MONAT field?

• ###### 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

• ###### 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.

• ###### 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?

• ###### Problem with Set Analysis with a period of time

Oh, I see ...

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

• ###### Re: Problem with Set Analysis with a period of time

It is still not working.

I tried your example like following:

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

Is still get the turnover in 2010 of the whole year.

If  I select the month manually from Jan to Aug I have the right turnover.

I do not understand it?

See the screenshots:

Without selection:

With selection:

• ###### 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

JARECDAT,

Year(JARECDAT) as Jahr,

month(JARECDAT) as Monat,

1 as Umsatz;

Today()-RecNo() as JARECDAT

AutoGenerate (1000);

Message was edited by: dgudkov

• ###### Re: Problem with Set Analysis with a period of time

Did you try breaking it down like this to set the same end date last year? I am not that great with set analysis so the formula might be wrong. Should do something like find only JAREDAT betweet the first day of last year and yesterday-1 year

Sum({<JAREDAT={>=yearstart(max(JAREDAT),-1)<=date(day(max(JAREDAT)),month(max(JAREDAT)),Year(max(JAREDAT)-1))>}Umsatz)

• ###### Re: Problem with Set Analysis with a period of time

The solution from dgudkov is running perfectly. .

Now I have only one problem left ,-)

If I tick a month manually, it will show me the whole turnover, starting from 01.01 to the enddate of the ticked month.

Shell I use a "if ... then ... else" combination with getfieldselections(Monat)

Like:

if getfieldselections(Monat)=0,

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

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

or do you have a better idea?

• ###### Re: Problem with Set Analysis with a period of time

What kind of behavior do you need for selecting a month?

• ###### Re: Problem with Set Analysis with a period of time

Good morning,

I need to select a month or a quarter to compare the actual turnover with the turnover one year ago.

This function is working perfectly:

if(isnull(getfieldselections(Monat)),

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

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

Does anyone has an easier way?

Nachricht geändert durch NewQlikie