Qlik Community

Ask a Question

New to QlikView

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

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Set Analysis

Hi,

I am very new to qlikview so please help me.

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

v_Min_dt = addmonths(v_max_dt,-60)

Thanks in advance.

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:

LOAD ...

     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.

View solution in original post

5 Replies
Champion III
Champion III

try

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

Try this?

=sum({<date={">=$(=Date(AddYears(Max(MonthEnd(date)),-5)))<=$(=Date(Max(MonthEnd(date))))"}>}Sales)

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
Specialist III

Try below exp , a bit lengthy but works as expcted

=sum({<MonthYear= {'$(=monthname(AddYears( max(MonthYear),-2)))' , '$(=monthname(AddYears( max(MonthYear),-1)))','$(=monthname(AddYears( max(MonthYear),0)))'}>}Amount)


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:

LOAD ...

     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.

View solution in original post

Contributor
Contributor

thanks.. it worked