topic Re: Set Analysis with dates in QlikView

Set Analysis with dates

evansabres — Wed, 01 Mar 2017 19:12:36 GMT

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?

Re: Set Analysis with dates

aarkay29 — Wed, 01 Mar 2017 19:29:28 GMT

try this

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

Re: Set Analysis with dates

maxgro — Wed, 01 Mar 2017 19:30:32 GMT

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

Sum(

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

[Net Sold Qty])

Re: Set Analysis with dates

vishsaggi — Wed, 01 Mar 2017 19:30:34 GMT

May be try this?

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

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

Re: Set Analysis with dates

evansabres — Wed, 01 Mar 2017 19:31:20 GMT

This returns a value of '0' for every line

Re: Set Analysis with dates

evansabres — Wed, 01 Mar 2017 19:34:08 GMT

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

Re: Set Analysis with dates

evansabres — Wed, 01 Mar 2017 19:36:08 GMT

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

Re: Set Analysis with dates

vishsaggi — Wed, 01 Mar 2017 19:36:51 GMT

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

Re: Set Analysis with dates

evansabres — Wed, 01 Mar 2017 19:40:21 GMT

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

Re: Set Analysis with dates

vishsaggi — Wed, 01 Mar 2017 19:56:28 GMT

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])

Re: Set Analysis with dates

evansabres — Wed, 01 Mar 2017 19:57:47 GMT

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

Re: Set Analysis with dates

vishsaggi — Wed, 01 Mar 2017 20:00:07 GMT

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 ?

Re: Set Analysis with dates

evansabres — Wed, 01 Mar 2017 20:01:14 GMT

Returns a value of '0'

Re: Set Analysis with dates

vishsaggi — Wed, 01 Mar 2017 20:02:30 GMT

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

Re: Set Analysis with dates

evansabres — Wed, 01 Mar 2017 20:03:49 GMT

attached

Re: Set Analysis with dates

maxgro — Wed, 01 Mar 2017 20:05:44 GMT

look at the attachment

Re: Set Analysis with dates

evansabres — Wed, 01 Mar 2017 20:09:25 GMT

I apologize, I do not follow the attachment

Re: Set Analysis with dates

maxgro — Wed, 01 Mar 2017 20:19:28 GMT

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

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

Re: Set Analysis with dates

vishsaggi — Wed, 01 Mar 2017 20:21:54 GMT

Is this what you want to look in your table ?