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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

try

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

Highlighted
Creator II
Creator II

Hi,

     Try peek() in script.

Peek(Sales,-1)+Sales as prev_Sales


Thanks:

Tushar

Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

maybe this:

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


I hope it helps.

Highlighted
Partner
Partner

it never work as expected

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

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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..