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.

[newqlikie]

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:

[#3 · Accepted solution]

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

[#4]

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)

[newqlikie]

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?

[#6]

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

[newqlikie]

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