Solved: Set Analysis with dates - Qlik Community

evansabres · ‎2017-03-01

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?

maxgro · ‎2017-03-01

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

View solution in original post

aarkay29 · ‎2017-03-01

try this

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

maxgro · ‎2017-03-01

if you have a key field in the table you can try

Sum(

{<KeyAutoNumber = {"=[Trans Date]=nweventDTE-1)" } >}

[Net Sold Qty])

vishsaggi · ‎2017-03-01

May be try this?

= Sum({< [Trans Date] = {"= $(= Date(Date#(nwEVENTDTE, 'DD/MM/YYYY')-1, 'DD/MM/YYYY'))"} >}[Net Sold QTY])

OR

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

evansabres · ‎2017-03-01

This returns a value of '0' for every line

evansabres · ‎2017-03-01

This also returns a value of '0' for every record

evansabres · ‎2017-03-01

None of the fields in the table are key fields unfortunately.

vishsaggi · ‎2017-03-01

Can you we know your dimensions you are using and other expressions you have, can you share a sample app you are working on?

evansabres · ‎2017-03-01

My dimension is called nwEVENTDTE and is formatted as 10/13/2016

[Net Sold Qty] is the sum of tickets distributed

[Trans Date] is the date of transaction formatted as 10/13/2016

I ultimately am trying to create a table that shows the nwEVENTDTE as the dimension, the total distributed ([Net Sold Qty]) and the [Net Sold Qty] one day before the nwEVENTDTE (i.e. when the [Trans Date] = nwEVENTDTE - 1 day

vishsaggi · ‎2017-03-01

Did you try the first expression i sent you. Do one thing using a text box object copy paste this expression and see what value you are getting, if you get nothing it means your expression is not calculating correctly. Just try this in text box and let us know what you see?

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

Set Analysis with dates

Related Topics