Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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
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)
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?
What kind of behavior do you need for selecting a month?
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