Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
ariel_klien
Valued Contributor

Set analysis with different variables

Hello all,

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

DepartmentFromMonthToMonth
A121
B126
C123

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:

DepartmentMonthNameSum
AAug. 16sum({<Date={">=01/08/2015<=01/07/2016"},MonthName=>} Amount)
AJuly 16sum({<Date={">=01/07/2015<=01/06/2016"},MonthName=>} Amount)
AJune 16sum({<Date={">=01/06/2015<=01/05/2016"},MonthName=>} Amount)
BAug. 16sum({<Date={">=01/01/2015<=01/02/2016"},MonthName=>} Amount)
BJuly 16sum({<Date={">=01/12/2014<=01/01/2016"},MonthName=>} Amount)
CAug. 16sum({<Date={">=01/06/2015<=01/05/2016"},MonthName=>} Amount)

how can i achieved that?

BR

Ariel

1 Reply

Re: Set analysis with different variables

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