Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Set Analysis and Yeartodate

Hi All

Could you please explain the meaning of Set Analysis given below

sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} Sales )

Further how can i change this to Yeartodate Calculation, further is there any pdf to learn about set analysis

i would like to learn to calculate YTD current year as well as previous year

Ishfaque Ahmed

Evan Kurowski

Jagan Mohan

Best Regards

Jayanthan

Tags (4)
1 Solution

Accepted Solutions
evan_kurowski
Valued Contributor

Re: Set Analysis and Yeartodate

Jayanthan Sivaloganathan wrote:

Hi All

Could you please explain the meaning of Set Analysis given below

sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} Sales )

Further how can i change this to Yeartodate Calculation, further is there any pdf to learn about set analysis

i would like to learn to calculate YTD current year as well as previous year

Ishfaque Ahmed

Evan Kurowski

Jagan Mohan

Best Regards

Jayanthan

Hello Jayanthan,

It looks like you are performing a nested set-analysis expression summing the field [Sales] using a two field filter, [YEAR] and [MONTH].

  • You are grabbing the maximum YEAR out of all possible years.
  • You have nested expression which grabs all months less than or equal to the maximum MONTH that is found in the maximum selected YEAR.

The granularity possible here is at the monthly level (meaning if you wanted to be able to jump to any calendar date and know what the YTD value for that given date was, you could not "split" a month using this method.  If the maximum date selected were the 15th of the current month and you wanted to compare against 1 month prior, you would only be able to compare 15 days from the current month against 28-31 days from the prior month).

Oftentimes I get requests for set-analysis comparison to calculate matching time frames.  If I am at the 6th day of the current year, the request is to compare against only the first 6 days of the prior year (and not the entire month of the prior year), because comparing 6 days from January of this year against the full 31-days from the January of the prior year will not yield a true apples-to-apples comparison until the current month is fully complete and populated with 31 days of data.  So the question often is.. "where do we stand against how we were doing on this day of the year, last year". 

To have the best granular control over your chronological set-analysis, form a date field that tags your fact information, and convert this date field to a pure integer field.  If the field only deals with Monthly level reporting, you can force each integer to either the first day or last day of each month via MonthStart() or MonthEnd() functions.

Once you have a DATE_NUM field, all set analysis is then applied to an integer field and no other calendar fields need to be involved to pluck any range you like from the entire data span:

DATENUM={">=$(Start)  <=$(End)" }

Plugging in a variety of start or end points doesn't change the syntax of the base expression.  Key points in timeline are always the same and will float based on user-selection:

Current Selected Date = Max(DATENUM)
Current Year Start =  Num(YearStart(Max(DATENUM)))

Prior Year Selected Date = Num(AddYears(Max(DATENUM)-1))

Prior Year Start = Num(YearStart(AddYears(Max(DATENUM),-1)))


Take for example a 60-day rolling calculation... it gets complicated when everything is packaged in full months only, and the year-turnover points start making plucking the right combinations of YEAR/MONTHs from the beginning of this year and the end of last year require additional logic, formulas, etc...  If you keep all set-analysis expressions centered around a DATENUM, they follow the same principle:

DATENUM={"> MAX(DATENUM)-60 <= MAX(DATENUM)"}

No matter where you reside in your result set, you should be able to get YTD from the selection point, and prior YTD of the same timeframe, down to the day level

If at any point your application starts caring about time of day and timestamps, you're already set-up to do this without having to change set-analysis structure.  Allow the DATENUM to switch from an integer to a float by introducing timestamp fractions into the data, no need to add new set-analysis filtering fields.

6 Replies

Re: Set Analysis and Yeartodate

Hi,

The basic YTD expression is

Sum({<DateField={

">=$(=Date(YearStart(max(DateField)))<=$(=Date(max(DateField)))"

}>}Sales)

Regards

Regards,
Prashant Sangle
Not applicable

Re: Set Analysis and Yeartodate

Hi Max

I do not understand , could you please explain

BR

Jayanthan

engishfaque
Valued Contributor III

Re: Set Analysis and Yeartodate

Dear Jayanthan,

Set Analysis:

It is a powerful qlikview feature and used for to compare different data sets. Set Analysis always enclosed with curly brackets {}.

Example:

1. Product sales of current year versus previous year.

Set Analysis Expression consist on three things which are listed below:

1. Set identifier

2. Set operator

3. Set modifier

Further, I recommended you this book "Qlikview Scripting by Matt Floyd" for quick understanding.

Kind regards,

Ishfaque Ahmed

Re: Set Analysis and Yeartodate

Hi,

DateField is nothing but your date On which you want to find YTD.

When you said

max(datefield) : It will gives you selected Date or if no date selected then it will give you default max date

YearStart(max(datefield)) : this will gives you Year Start date of max(dateField)

So in my expression I am doing simple calculation where

where i need Sum of sales where my date is inbetween Year start of that date to max(date) i.e. selected date.

I hope now you are clear.

Regards,

Regards,
Prashant Sangle
evan_kurowski
Valued Contributor

Re: Set Analysis and Yeartodate

Jayanthan Sivaloganathan wrote:

Hi All

Could you please explain the meaning of Set Analysis given below

sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} Sales )

Further how can i change this to Yeartodate Calculation, further is there any pdf to learn about set analysis

i would like to learn to calculate YTD current year as well as previous year

Ishfaque Ahmed

Evan Kurowski

Jagan Mohan

Best Regards

Jayanthan

Hello Jayanthan,

It looks like you are performing a nested set-analysis expression summing the field [Sales] using a two field filter, [YEAR] and [MONTH].

  • You are grabbing the maximum YEAR out of all possible years.
  • You have nested expression which grabs all months less than or equal to the maximum MONTH that is found in the maximum selected YEAR.

The granularity possible here is at the monthly level (meaning if you wanted to be able to jump to any calendar date and know what the YTD value for that given date was, you could not "split" a month using this method.  If the maximum date selected were the 15th of the current month and you wanted to compare against 1 month prior, you would only be able to compare 15 days from the current month against 28-31 days from the prior month).

Oftentimes I get requests for set-analysis comparison to calculate matching time frames.  If I am at the 6th day of the current year, the request is to compare against only the first 6 days of the prior year (and not the entire month of the prior year), because comparing 6 days from January of this year against the full 31-days from the January of the prior year will not yield a true apples-to-apples comparison until the current month is fully complete and populated with 31 days of data.  So the question often is.. "where do we stand against how we were doing on this day of the year, last year". 

To have the best granular control over your chronological set-analysis, form a date field that tags your fact information, and convert this date field to a pure integer field.  If the field only deals with Monthly level reporting, you can force each integer to either the first day or last day of each month via MonthStart() or MonthEnd() functions.

Once you have a DATE_NUM field, all set analysis is then applied to an integer field and no other calendar fields need to be involved to pluck any range you like from the entire data span:

DATENUM={">=$(Start)  <=$(End)" }

Plugging in a variety of start or end points doesn't change the syntax of the base expression.  Key points in timeline are always the same and will float based on user-selection:

Current Selected Date = Max(DATENUM)
Current Year Start =  Num(YearStart(Max(DATENUM)))

Prior Year Selected Date = Num(AddYears(Max(DATENUM)-1))

Prior Year Start = Num(YearStart(AddYears(Max(DATENUM),-1)))


Take for example a 60-day rolling calculation... it gets complicated when everything is packaged in full months only, and the year-turnover points start making plucking the right combinations of YEAR/MONTHs from the beginning of this year and the end of last year require additional logic, formulas, etc...  If you keep all set-analysis expressions centered around a DATENUM, they follow the same principle:

DATENUM={"> MAX(DATENUM)-60 <= MAX(DATENUM)"}

No matter where you reside in your result set, you should be able to get YTD from the selection point, and prior YTD of the same timeframe, down to the day level

If at any point your application starts caring about time of day and timestamps, you're already set-up to do this without having to change set-analysis structure.  Allow the DATENUM to switch from an integer to a float by introducing timestamp fractions into the data, no need to add new set-analysis filtering fields.

Not applicable

Re: Set Analysis and Yeartodate

Hi Evan

Thank you for your extended support, Its highly appreciated.

Best Regards

Jayanthan

Community Browser