Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sergeybelov
New Contributor III

The problem with Set analysis, how to change current selection

I have a table with date month and earn sum. I try to get the last year sum  earn happended relatively  current month selection like this:

sum({$<Month={"$(=AddMonths(Only(Month),-12))"}>} earn)

but get only zero. This code on QlikView on the same data works properly. Please help. i'm stuck with this problem ((

8 Replies
MVP
MVP

Re: The problem with Set analysis, how to change current selection

What is the format for the Month field? Also, which field are you making selection in?

omarbensalem
Esteemed Contributor

Re: The problem with Set analysis, how to change current selection

Your month field must be numerci (01,02,03,04...,12)

sergeybelov
New Contributor III

Re: The problem with Set analysis, how to change current selection

The type is 'date'. Expression "AddMonths(Only(Month),-12))" stored as a label shows me correct month value.

I make select in two fields:

  1. Month
  2. Store
sergeybelov
New Contributor III

Re: The problem with Set analysis, how to change current selection

My month is not numeric value, it is date.

sergeybelov
New Contributor III

Re: The problem with Set analysis, how to change current selection

I can't understand the following: when I write

sum({$<[Month]={"$(=AddMonths(max([Month]),-12))"}>} [Earn])//max([Month])=September 2017 (type - date)

and select two months: September 2016 and September 2017 QlikSense puts the correct earn value of September 2016.

When I select only September 2017 and expect the same result I receive zero. However  QlikView shows correct value in this case it modify current month selection to necessary value of months last year.

Why there are different behavior in QlikView and QlikSense?

sergeybelov
New Contributor III

Re: The problem with Set analysis, how to change current selection

It is curious: if I select Month in Filter field the Set analysis does not work properly, but if I select the Month in the Table the behaviour would be right. Why is that?

sergeybelov
New Contributor III

Re: The problem with Set analysis, how to change current selection

I guess the problem is in the fields. I select the Filter with values:

=MonthName([Month],0)

but in Set analysis use only values Month.

How can I use human readble MonthName in the filter and use Set analysis with Month value at the same time? Ir works in QlikView but doesn't wokt in QlikSense

sergeybelov
New Contributor III

Re: The problem with Set analysis, how to change current selection

The solution is to use Set analysis like this:

sum({$<'=MonthName([Month],0)'={"$(=MonthName(only([Month]),-12))"}>} [Earn])