Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shweta14_singh8
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
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Chanty4u
MVP
MVP

try

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

Anil_Babu_Samineni

Try this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
sasikanth
Master
Master

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

shweta14_singh8
Contributor
Contributor
Author

thanks.. it worked