# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Contributor

## Set Analysis

Hi,

I have a requirement for ex. If i select a value aug 2017, then my pivot table should show 5 years data i.e aug 2017, aug 2016, aug 2015, aug 2014 & aug 2013. The value should contain only august data not other months.

Whether this logic can be implemented within a single expression??

Below is my expression :

=sum({<date={">=\$(v_Min_dt)<=\$(v_Max_dt)"}>}Sales)

v_Max_dt= 30/08/2017

1 Solution

Accepted Solutions

The easiest method to perform a period selection with holes is to split your date field into a set of calendar fields. Add these to the LOAD of the table containing date:

date,

Month(date) AS dateMonth,

Year(date) AS dateYear,

...

FROM ...;

Now Set Analysis can be programmed in a simple way:

=Sum({<date=, dateMonth={'aug'}, dateYear={">=\$(=Max(dateYear)-4)<=\$(=Max(dateYear))"}>} Sales)

Your regional setting for month names may be different, so make sure that you change the 'aug' string value into a value that works for you.

5 Replies
Champion III

try

=sum({<date={">=\$(=v_Min_dt)<=\$(=v_Max_dt)"}>}Sales)

Try this?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Specialist III

Try below exp , a bit lengthy but works as expcted

calculate MonthYear from script

MonthName(Date) as MonthYear

The easiest method to perform a period selection with holes is to split your date field into a set of calendar fields. Add these to the LOAD of the table containing date:

date,

Month(date) AS dateMonth,

Year(date) AS dateYear,

...

FROM ...;

Now Set Analysis can be programmed in a simple way:

=Sum({<date=, dateMonth={'aug'}, dateYear={">=\$(=Max(dateYear)-4)<=\$(=Max(dateYear))"}>} Sales)

Your regional setting for month names may be different, so make sure that you change the 'aug' string value into a value that works for you.

Contributor

thanks.. it worked