topic Re: Year-to-month set analysis in QlikView

Year-to-month set analysis

Wed, 29 May 2013 08:22:43 GMT

Hi there,

I'm trying to show a static year-to-month sales achievement vs. Budget with the following formula:

sum(

{1<[Year={$(=Year(Today()))}>}

Sales)

sum(

{1<Year={$(=Year(Today()))}>}

Budget)

My problem is, that it only shows sales achievement vs. the budget for the whole year.

I need a dynamic set analysis expression, that also sets a time frame for months i.e. cumulative Budget from Jan to May. As soon as we're in June it should set Jan to June etc..

Is there any way to define a dynamic Year-to-month time frame?

i.e. month= "first month of the year" to "Current month"

thanks a lot!

Re: Year-to-month set analysis

somenathroy — Wed, 29 May 2013 08:56:18 GMT

If you have Date field in your datamodel, then try as below:

sum({1<Date_Field={>=$(=YearStart(Today())) <=ToDay()}>}Sales)

sum({1<Date_Field={>=$(=YearStart(Today())) <=ToDay()}>}Budget)

Regards,

Som

Re: Year-to-month set analysis

Wed, 29 May 2013 09:17:45 GMT

Hi,

Sum({<Year={=$(Year(Today()))},Month=,Day=>}Sales)/Sum({<Year={=$(Year(Today()))},Month=,Day=>}Budget)

Re: Year-to-month set analysis

Wed, 29 May 2013 09:24:56 GMT

Hi there,

thanks for your quick response.

This doesn't seem to work... we have a date Field %Datum in our datamodel, but when i use your expression above it looks like this:

all following formulas and expressions are marked red and inactive...

Re: Year-to-month set analysis

Wed, 29 May 2013 09:41:46 GMT

Hi there,

thanks for your response.

As far as i know

sum(

{1<[Year={$(=Year(Today()))}>}

Sales)

sum(

{1<Year={$(=Year(Today()))}>}

Budget)

already makes sure, that neither Month nor Day would influence the data that is shown.

I need to set the time frame for the Budget:

let's say it looks like the following,

Jan 1.300.000 € budget

Feb 1.000.000 € budget

Mar 1.100.000 € budget

Apr 1.000.000 € budget

May 1.100.000 € budget

June 1.000.000 € budget

Jul 1.200.000 € budget

Aug 1.000.000 € budget

Sep 1.000.000 € budget

Oct 1.300.000 € budget

Nov 1.000.000 € budget

Dec 1.000.000 € budget

Budget for 2013: 13.000.000 €

We've got May 31st and Sales YTD 5.000.000 €

The Budget Year-to-month would be 5.500.000 €

That gives us a target achievement of about 91% Year-to-month

starting june, the budget year-to-month would accumulate to 6.500.000 €

and we would have a target achievement rate of 77% year-to-month

so I want the set analysis to set the budget dynamically according to the current month + all previous months of this year.

Re: Year-to-month set analysis

somenathroy — Wed, 29 May 2013 09:53:58 GMT

Hi,

Use the expression below:

sum({<%Datum = {'>=$(=YearStart(ToDay()))<=$(=MonthEnd(ToDay()))'}>}Budget)

If '%Datum' has date format as DD-MM-YYYY, then replace

ToDay() with Date(Date#(ToDay(),"DD-MM-YYYY"),"DD-MM-YYYY")

Regards,

som

Re: Year-to-month set analysis

Wed, 29 May 2013 10:01:29 GMT

Hi,

Could it be, that our QV (Ver. 11.11282 (SR1 64bit)) Version can't handle operators like >, <, >=, <= in a set analysis?

I'm constantly getting "0" as a result...

Re: Year-to-month set analysis

somenathroy — Wed, 29 May 2013 10:02:51 GMT

Can you pls upload a sample QVW?

Re: Year-to-month set analysis

Wed, 29 May 2013 10:26:04 GMT

Hi all,

it seems, that we've solved the problem by adding the following expression to our set analysis:

,Month={"<= $(=Num(Month(Today()-1)))"}

This seems to work for us.

Thanks to everyone who responded!