Problem by chossing different periods

[newqlikie]

Hello together,

at the moment, I am struggling with following problem:

I have a database with daily turnovers (Monday to Friday). The turnover begins at 05.01.2009 and ends yesterday.

For example:

Umsatz (Turnover)	JARECDAT
123.456 EUR	02.05.2011
32.345 EUR	03.05.2011
145.453 EUR	04.05.2011
93.929 EUR	05.05.2011
100.011 EUR	06.05.2011

Now I have separated the dates to "Day", "Month" and "Jear".

Now I would like to compare the turnover from this month with the same timeperiod one year ago.

For example: 01.09.2011 till 20.09.2011 is to compare with 01.09.2010 till 20.09.2010

If I use following function, I will always get the turnover one year ago till 30.09.2010 and not till 20.09.2010

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

It also could be the actual quarter starting from 01.07.2011 till 20.09.2011 which has to be compared with 01.07.2010 till 20.09.2010.

The function above will always give the date starting 01.07.2010 till 30.09.2010.

What do I do wrong?

I appreciate your help.

Regards

   NewQlikie

[#2]

U have to be carefull with number interpretation of the date...

I had some problem, but this seems to be ok... let me know..

This is the expression of total turnover:

if(Sum(Umsatz)>0,Sum({$<Jahr={$(=Only(Jahr)-1)},Tag={"<=$(#=max({$<Monat={'=Monat=max(Monat)'}>}Tag))"}>}Umsatz))

[newqlikie]

Hi,

this one is almost working for a total turnover in between months with one problem.

What happens is:

If I choose July 2011 (which is from 03.07. till 29.07.2011).

I get the turnover right in 2011.

But in 2010 the fomula picks (03.07. till 29.07.11) and not till 30.07.2011

I only need the limitation of the day in the actual month. If a month is already finished, I need the whole turnover of that month or timeperiode.

Here is an other explanation:

Turnover in the actual timeperiod:

the full last two month in 2011 (Jul and Aug) +  the actual month till yesterday in 2011 (01.09. - 21.09.2011) ->  I choose "2011" and "Jul, Aug and Sep"

Compared turnover last year:

the full last two month in 2010 (Jul and Aug) +  the actual month till yesterday in 2010 (01.09. - 21.09.2010)

[#4]

Hi,

tell me if this is ok :

Sum({$<Jahr={$(=Only(Jahr)-1)},Monat-={$(#=(max(Monat)))}>+

<Jahr={$(=Only(Jahr)-1)},Monat={$(#=(max(Monat)))},Tag={"<=$(#=max({$<Monat={'=Monat=max(Monat)'}>}Tag))"}>}Umsatz)

C u ,

Stefano

[newqlikie]

Hi Stefano,

I'm sorry,but it is also not working.

Your function is taking the whole "September" (01.09. till 30.0.2010)  from last year, if I only choose "Sep" and "2011".

Any other idea?

[#6]

Hi,

hi think the problem is the String Value of Month,

May u try a reload with a num format of month?

Than u can try to post me that.

Thanks.

Stefano

[newqlikie]

Hi Stefano,

how do I do a reload of the month varlues in a num format?

[#8]

try with this:

num(month(JARECDAT))

Ste.

[newqlikie]

Hi Stefano,

enclosed the reload with the month in num format.

It has not changed anything in relation to the formulas.

[#10]

Hi,

let me know if the first expression is ok.

Have confidence in me and in Qlik..

Ste.

[newqlikie]

Hi Stefano,

I am really sorry to say, that the function is still not working, but I think, you are almost with it.

The problem is, if you tick in month "07" and in year "2011", the total turnover was 10.079,73 EUR and not 9.908,67 EUR.

That problem occurs, because the actual year has different workingdays, than in 2010. Therefore I need the total turnover (beginning from the first to the last day) for each month, except for the actual month.

Here is another example:

Total turnover of the past months in the past years:

First day of a month in the past ("01.month.past-year") till to the last day of a month in the past (for Aug. "31.08.past-year", or for Jun. "30.07.past-year")

Total turnover of the actual month in the past year:

First day of the actual month  ("01.month.past-year") till one day before the actual date (today is 23.09.2011 => "22.07.2010").

Total turnover of the actual month in the actual year:

First day of the actual month  ("01.month.actual year") till one day before the actual date (today is 23.09.2011 => "22.07.2011").

Thanks again for your further help.