Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Date related set analysis not working

Hi to all i got a problem where my set analysis is not working as expected

this is my set analysis

Sum({<Date1 = {">=$(AddMonths(MonthStart(Max(Date1)),-1))"}>}[Amount])

where if i filter Month = Feb it must give me sum of Jan and Feb

but it only gives me sum of Feb only and i dont know why

this is my inline code

i also include my QVF

Sales:

LOAD Date(Date1) as Date1, Amount INLINE [


    Date1,Amount


    2018-01-01,100


    2018-02-02,200



];


Calendar:

LOAD Date(Date1) as Date1 INLINE [


    Date1


    2018-01-01

    2018-01-02

    2018-01-03

    2018-01-04

    2018-01-05

    2018-01-06

    2018-01-07

    2018-01-08

    2018-01-09

    2018-01-10

    2018-01-11

    2018-01-12

    2018-01-13

    2018-01-14

    2018-01-15

    2018-01-16

    2018-01-17

    2018-01-18

    2018-01-19

    2018-01-20


    2018-02-01

    2018-02-02

    2018-02-03

    2018-02-04

    2018-02-05

    2018-02-06

    2018-02-07

    2018-02-08

    2018-02-09

    2018-02-10

    2018-02-11

    2018-02-12

    2018-02-13

    2018-02-14

    2018-02-15

    2018-02-16

    2018-02-17

    2018-02-18

    2018-02-19

    2018-02-20

  

];


Thanks in Advance

15 Replies
Highlighted
Partner
Partner

what you get with =AddMonths(MonthStart(Max(Date1)),-1)  expression in a text object ?

Highlighted
Specialist III
Specialist III

May be this:

Sum({<Date1={">=$(=MonthStart(Max(Date1),-1))"}>}[Amount])

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Highlighted
Partner
Partner

try replacing your month filter with the below calculated dimension as filter:

=Aggr( only( Month(Date1) ), Date1 )

using this expression:

Sum( {< Date1 = {">=$(=AddMonths(MonthStart(Max(Date1)),-1))"} >} [Amount] )

it should works.

Highlighted
Partner
Partner

please mark the answers as helpful or correct if applicable to help others members.

thanks.

Highlighted
Partner
Partner

wow thanks mate.... i'm wondering whats the purpose of that 1 in the sum({1....

Highlighted
Partner
Partner

Hi Reynaldson,

The 1 indicates that you are applying the formula to the entire dataset, ignoring any filters. If you want to Ignore the selection made on specific fields, you can also use something like sum({<MonthField=, YearField=>} Amount), this ignores any selections made on the MonthField and YearField values in your dataset.

Cheers,

LA.