Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raceberos
Partner - Contributor III
Partner - Contributor III

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
agigliotti
Partner - Champion
Partner - Champion

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

Thiago_Justen_

May be this:

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

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
agigliotti
Partner - Champion
Partner - Champion

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.

agigliotti
Partner - Champion
Partner - Champion

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

thanks.

raceberos
Partner - Contributor III
Partner - Contributor III
Author

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

lalphonso
Partner - Contributor III
Partner - Contributor III

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.