Set Analysis with dates

[evansabres]

I am attempting to reference a date in a set analysis expression to give me a number as a result.

I want to be able to know the [Net Sold Qty] of an nwEVENTDTE when the [Trans Date] = nweventDTE-1

This is what I have for an expression currently.

=SUM({$<[Trans Date]={"(nwEVENTDTE-1)"},}>}[Net Sold Qty])

However, this returns a value of '0' which is incorrect. Have I formatted the expression incorrectly?

[evansabres]

In a text box, this returns a value of '0'

[vishsaggi]

Try this?

= Sum({< [Net Sold QTY] = {"= [Trans Date] = Date(nwEVENTDTE-1)"} >} [Net Sold QTY])

Can you share a sample or expected output values you want to see ?

[evansabres]

Returns a value of '0'

[vishsaggi]

TIme to send us some data now...Can you share some sample data values ?

[evansabres]

attached

[maxgro]

look at the attachment

[evansabres]

I apologize, I do not follow the attachment

[maxgro · Accepted solution]

script to create a test table with 2 dates, d1 and d2 and a measure val, as your question

x:

load

  *,

  recno() as id,                      // key

  -1*(d1=(d2-1)) as flag;          // flag to identify the record with date1 = date2 -1

load

  date(makedate(2017) + floor(rand()*90)) as d1,  // one of your date, random first 90 days of 2017

  date(makedate(2017) + floor(rand()*90)) as d2,  // the second of your date, random first .............

  ceil(rand()*100) as val                                     // your net sold qty, random

AutoGenerate 200;                    // 200 records

chart with date1 as dimension and 3 different ways (expressions) to identify the sum(val) for date1=date2-1

expression1    sum(if...)

expression2    with set analysis, sum the val when flag=1, the flag i made in the script

expression3    with set analysis and the key field I make in the script, id

[vishsaggi]

Is this what you want to look in your table ?