topic Set Analysis in QlikView

Set Analysis

shweta14_singh8 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Set Analysis

Chanty4u — Wed, 04 Jul 2018 17:14:34 GMT

try

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

Re: Set Analysis

Anil_Babu_Samineni — Thu, 05 Jul 2018 03:55:54 GMT

Try this?

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

Re: Set Analysis

sasikanth — Thu, 05 Jul 2018 05:26:04 GMT

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

Re: Set Analysis

Peter_Cammaert — Thu, 05 Jul 2018 07:08:08 GMT

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.

Re: Set Analysis

shweta14_singh8 — Fri, 06 Jul 2018 06:17:13 GMT

thanks.. it worked