Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER 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 - Master III
Partner - Master III

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 - Master III
Partner - Master III

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 - Master III
Partner - Master III

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.