Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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

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

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)

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

Peter_Cammaert

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

shweta14_singh8
Contributor
Contributor

thanks.. it worked