topic Re: Problem by chossing different periods in QlikView

Problem by chossing different periods

newqlikie — Wed, 21 Sep 2011 14:19:51 GMT

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

Re: Problem by chossing different periods

Wed, 21 Sep 2011 14:35:27 GMT

HI,

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

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

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

C u,

Stefano

Problem by chossing different periods

newqlikie — Wed, 21 Sep 2011 14:39:29 GMT

Hi Stefano,

no, this is not working.

Problem by chossing different periods

Wed, 21 Sep 2011 14:44:52 GMT

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

Problem by chossing different periods

Wed, 21 Sep 2011 14:55:08 GMT

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.

Re: Problem by chossing different periods

newqlikie — Thu, 22 Sep 2011 07:21:08 GMT

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)

Re: Problem by chossing different periods

Thu, 22 Sep 2011 07:33:35 GMT

Hi,

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

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

c u,

Stefano

Re: Problem by chossing different periods

newqlikie — Thu, 22 Sep 2011 07:55:12 GMT

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!

Re: Problem by chossing different periods

Thu, 22 Sep 2011 08:40:26 GMT

Hi,

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

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

Stefano

Re: Problem by chossing different periods

newqlikie — Thu, 22 Sep 2011 11:04:36 GMT

Hi,

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

Re: Problem by chossing different periods

Thu, 22 Sep 2011 11:16:49 GMT

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

Re: Problem by chossing different periods

newqlikie — Thu, 22 Sep 2011 11:58:23 GMT

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)

Re: Problem by chossing different periods

Thu, 22 Sep 2011 12:40:38 GMT

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

Problem by chossing different periods

newqlikie — Thu, 22 Sep 2011 12:45:40 GMT

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?

Re: Problem by chossing different periods

Thu, 22 Sep 2011 13:26:18 GMT

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

Problem by chossing different periods

newqlikie — Thu, 22 Sep 2011 13:59:01 GMT

Hi Stefano,

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

Problem by chossing different periods

Thu, 22 Sep 2011 14:18:40 GMT

try with this:

num(month(JARECDAT))

Ste.

Re: Problem by chossing different periods

newqlikie — Thu, 22 Sep 2011 14:30:04 GMT

Hi Stefano,

enclosed the reload with the month in num format.

It has not changed anything in relation to the formulas.

Re: Problem by chossing different periods

Thu, 22 Sep 2011 14:39:03 GMT

Hi,

let me know if the first expression is ok.

Have confidence in me and in Qlik..

Ste.

Re: Problem by chossing different periods

newqlikie — Fri, 23 Sep 2011 06:13:55 GMT

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.