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]

HI,

have u tried {$(#=Only(Jahr)-1)}

instead of  {$(=Only(Jahr)-1)}?

and MONAT={"<=$(#=month(JARECDAT))"}>}?

C u,

Stefano

[newqlikie]

Hi Stefano,

no, this is not working.

[#4]

but in the table  u have posted umsatz field is a string.

Have u considered this?

Otherwise try to post a little qvw with some example data.

c u,

Stefano

[#5]

If the problem is only the day of 9 month, this is correct cause you make "<=$(#=month(JARECDAT))"

This year u have 20 days but 1 years ago 30 days.

[newqlikie]

Hi Stefano,

thanks for your comment.

The Field "Umsatz" ist not a string, it is a "numeric" field (without EUR). Sorry.

The problem I have is, that I need to compare the exact timeperiod which I choose within one year with the same timperiod the year before.

I attached a sample.

1.) To see the real problem, please choose the actual year (2011) and the actual month (Sep).

2.) Then you see, which days should be included. (starting from 01 till 21 without weekends)

3.) Now you get following turnover:

4.) If you now chosse also the days (starting from 01 till 21), you will get following turnover:

5) The next problem will be, if I choose the actual quarter.

- This year would be the date 01.07.2011 till 21.09.2011

- The compared year would be 01.07.2010 till 30.09.2010 (but it should be 21.09.2010)

[#7]

Hi,

if u use a set like this also for the year before expression?

Tag={"<=$(#=max(Tag))"}

c u,

Stefano

[newqlikie]

Hi Stefano,

this would be my function:

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

but it is not woking, because if I choose a quarter (in my example "quarter 3", the max(day) would be 31 and for that, it will sum all the turnover starting 01.07.2010 till 30.09.2010. That timeperiod would be compared with 01.07.2011 till 21.09.2011. This is my problem.

If I use above mentioned formula by choosing only one month, it works perfectly!

Do you have another idea?

Message modified by NewQlikie:

Above mentioned formula is not working properly. If I choose only one day in Sept. (e.g. 15.09.2011), this formula give me back the turnover from 01.09.2010 till 15.09.2010 and not only the turnover for 15.09.2010!

[#9]

Hi,

i don't test this, but u can try something like this:

Tag={"<=$(#=max({$<Monat={$(#=max(Monat))}>}Tag))"}

Stefano

[newqlikie]

Hi,

I'm sorry, but I do not get any result with this formula and I am not to good with "set analysis".