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. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
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

1 Solution

Accepted Solutions
lalphonso
Partner - Contributor III
Partner - Contributor III

Hi,

When you make a selection, your formula also works with the filtered selection.

try

=sum({1<Date1 = {'>=$(AddMonths(MonthStart(Max(Date1)),-1))'}>}Amount)

Regards,

LA

View solution in original post

15 Replies
nsetty
Partner - Creator II
Partner - Creator II

try

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

tush
Creator II
Creator II

Hi,

     Try peek() in script.

Peek(Sales,-1)+Sales as prev_Sales


Thanks:

Tushar

raceberos
Partner - Contributor III
Partner - Contributor III
Author

still the same mate... btw thanks for the reply...

lalphonso
Partner - Contributor III
Partner - Contributor III

Hi,

When you make a selection, your formula also works with the filtered selection.

try

=sum({1<Date1 = {'>=$(AddMonths(MonthStart(Max(Date1)),-1))'}>}Amount)

Regards,

LA

raceberos
Partner - Contributor III
Partner - Contributor III
Author

no i don't need to do it in the script since i need to be sum dynamically the amount according to its filter..

agigliotti
Partner - Master III
Partner - Master III

maybe this:

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


I hope it helps.

raceberos
Partner - Contributor III
Partner - Contributor III
Author

it never work as expected

hmmmm i was wondering where did i go wrong in this formula...

agigliotti
Partner - Master III
Partner - Master III

try first replacing the real value as below:

Sum( {< Date1 = {">=2018-01-01"} >} [Amount] )


and also create a text object with the below expression:


=AddMonths(MonthStart(Max(Date1)),-1)


to see what you get.

raceberos
Partner - Contributor III
Partner - Contributor III
Author

Hi Andrea

thanks for the reply, actually i already did that

test1.JPG

but still no success

Thanks you very much i really appreciate your help..