Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Year-to-month set analysis

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!

1 Solution

Accepted Solutions
Not applicable

Re: Year-to-month set analysis

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!

View solution in original post

8 Replies
somenathroy
Contributor III

Re: Year-to-month set analysis

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

Not applicable

Re: Year-to-month set analysis

Hi,

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

Not applicable

Re: Year-to-month set analysis

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:

pic.PNG

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

Not applicable

Re: Year-to-month set analysis

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.

somenathroy
Contributor III

Re: Year-to-month set analysis

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

Not applicable

Re: Year-to-month set analysis

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

somenathroy
Contributor III

Re: Year-to-month set analysis

Can you pls upload a sample QVW?

Not applicable

Re: Year-to-month set analysis

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!

View solution in original post