Set analysis for date field within next month (future months)

[jkflash1964]

Hi,

First post question.  Switching from Crystal reports to Qlik Sense, so lots to learn.

I have a field, Required Date, for Sales Orders.  So I am trying to figure out a Set Analysis formula to determine if a given Required Date is within a given month.  Imagine I would need one for Current Month, Next month and out 11 months.  If the Required Date is within that particular month's range, then would Sum (SO$).

Thanks in advance for the help.

[Taoufiq_Zarra]

@jkflash1964  welcome on qlik

can you share a sample data and the expected output ?

or just try

if(date>=Date1 and date<Date2,sum(So)) someting like this

[jkflash1964]

Below is some sample data.

So I was thinking that there would be a formula for each month starting from current month, then going out 11 months.  If the required date was in the particular month, then would be part of the sum for SO$.

In Crystal, I used this formula for month after current month (next month SO$) but would like to update it for set analysis:  if {@requiredDate} in dateserial(year(currentdate),month(currentdate)+1,1) to dateserial(year(currentdate),month(currentdate)+2,1-1)
then {@SO$}
else
0

Reqd Date   SO$
2/5/2021      $72,682.80  Order 1
2/15/2021      $41,006.85  Order 2
12/14/2020 $36,867.60  Order 3
12/10/2020 $19,584.00  Order 4

I would expect that Sum(SO$) for December = 56,481.60,  Sum(SO$) for May = 113,689.65

[jkflash1964]

added some more info to what i am looking for.

another acceptable way to look at month range is to have a formula be for Jan 2021.  Which could then be altered to be for Feb 2021, etc.

[jkflash1964 · Accepted solution]

after scouring the forum, finally found a month range formula that will do for now.

Sum({<Financial_Line_Required_Date={"$(= '>=' & '1/1/2021' & '<=' & '1/31/2021')"}>} OpenSO_Dollar)