Set Analysis

[shweta14_singh8]

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.

[Chanty4u]

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)

[sasikanth]

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 · Accepted solution]

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]

thanks.. it worked