Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

If statement combined with set analysis

Hi everyone!

Im new to qlik sense and having problems with combining set analysis with an if statement. I am making a line chart and only want the last twelve months to be shown, but I also want a filter on a dimension. Its when Im adding the if statement that it stops working.

CustomerGroup=Numeric dimension

Indicator=Sum(NBCustomer)

if(CustomerGroup=1, Sum({$<Month=, Year=, Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer]))

This part works seperatly:

Sum({$<Month=, Year=, Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])


Please help!!


Kind regards,

Sofia

1 Solution

Accepted Solutions
Ivan_Bozov
Valued Contributor

Re: If statement combined with set analysis

Try this for your expression:

Sum({$<CustomerGroup={'1'}, Month=, Year=, Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

8 Replies
agigliotti
Honored Contributor II

Re: If statement combined with set analysis

please tell us which dimensions and measures you are using in line chart.

Not applicable

Re: If statement combined with set analysis

Hello!

CustomerGroup=Numeric dimension

Indicator=Sum(NBCustomer)

Date=date dimension

agigliotti
Honored Contributor II

Re: If statement combined with set analysis

why you need to use IF ?

Not applicable

Re: If statement combined with set analysis

I probably dont! If you have a better way I would very much appreciate it!

Kind regards,

Sofia

Ivan_Bozov
Valued Contributor

Re: If statement combined with set analysis

Try this for your expression:

Sum({$<CustomerGroup={'1'}, Month=, Year=, Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

Employee
Employee

Re: If statement combined with set analysis

Please try this:

Sum({$<CustomerGroup*={"1"},Month=, Year=, Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

agigliotti
Honored Contributor II

Re: If statement combined with set analysis

you can use set analysis also for CustomerGroup like below:


Sum({$<Month=, Year=, Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}, CustomerGroup = {"1"} >} [NbCustomer])


this will only filter CustomerGroup  = 1 regardless which CustomerGroup you selected, else you can use CustomerGroup *= {"1"} to take care also of CustomerGroup selections.

Not applicable

Re: If statement combined with set analysis

Thank you everyone! Now it works.

Kind regards,

Sofia