Set analysis with different variables

ariel_klien — Wed, 03 Aug 2016 06:40:54 GMT

Hello all,

I have a table with departments and month back to sum. like this:

Department	FromMonth	ToMonth
A	12	1
B	12	6
C	12	3

for example: if we are in Aug.16 we need to take in

Department A sum of amount Aug.15 - July. 16 >=AddMonths(Date,-1) <=AddMonths(Date,-12)

Department B sum of amount Jan.15 - Feb. 16 >=AddMonths(Date,-6) <=AddMonths(Date,-12)

Department A sum of amount June.15 - May. 16 >=AddMonths(Date,-3) <=AddMonths(Date,-12)

I put the dates inside variables:

vDateFrom=AddMonths(if(GetSelectedCount(MonthName)=1,MonthName,Max(MonthName)),-FromMonth)

vDateTo=AddMonths(if(GetSelectedCount(MonthName)=1,MonthName,Max(MonthName)),-ToMonth)

in my set analysis this is my expression:

sum({<Date={">=$(=$(vFromDate))<=$(=$(vToDate))"},MonthName=>} Amount)

the expression working fine when i chose only one department. when more then one possible vToMonth is null.

my final table need to be:

Department	MonthName	Sum
A	Aug. 16	sum({<Date={">=01/08/2015<=01/07/2016"},MonthName=>} Amount)
A	July 16	sum({<Date={">=01/07/2015<=01/06/2016"},MonthName=>} Amount)
A	June 16	sum({<Date={">=01/06/2015<=01/05/2016"},MonthName=>} Amount)
B	Aug. 16	sum({<Date={">=01/01/2015<=01/02/2016"},MonthName=>} Amount)
B	July 16	sum({<Date={">=01/12/2014<=01/01/2016"},MonthName=>} Amount)
C	Aug. 16	sum({<Date={">=01/06/2015<=01/05/2016"},MonthName=>} Amount)

how can i achieved that?

Ariel

Re: Set analysis with different variables

sunny_talwar — Wed, 03 Aug 2016 09:51:53 GMT

I think set analysis is evaluated once per chart. You might have to replicate what you are trying to do using if statement here.

Sum(If(Date >= AddMonths(Date,-ToMonth) and Date <= AddMonths(Date, -FromMonth), Amount)

UPDATE: I think you will need to use Aggr() function here and some other set analysis. May be if you can provide a sample which includes the date field, it might become easier to provide a solution here

topic Re: Set analysis with different variables in QlikView

Set analysis with different variables

Re: Set analysis with different variables