Average with Set analysis

[brunolelli87]

Hello guys, 
I'm trying to create a chart with Date as Dimension and the following expression:

=avg({1 <STCK={'CVCB3'}, DVPG={'> 03/01/2020'}>} VDAF)

I don't know why but it's not working, it shows NO DATA TO DISPLAY,  but I'm sure there are available data to display. 

My intention is to create a chart where Date (DVPG) is a dimension, and the expression is the average of VDAF values, from 03/01/2020 up to today!

How can I do it?

Thanks

[Lisa_P]

Try this:

=avg({1 <STCK={'CVCB3'}, DVPG={">03/01/2020"}>} VDAF)

[Vegar]

Notice that @Lisa_P is using double quotes, not single quotes on the date comparison.

In Set analysis use single quotes are used forsfor case sensitive string match where *, ?, =, >, < and ~ all are treated as any characters. (Your expression might have worked in an earlier version of QlikView/Qlik Sense, but that was a bug was fixed a couple of years ago)

[brunolelli87]

Thanks

And how could I convert that expression with a static date, to something like this:

=avg({1 <STCK={'CVCB3'}, DVPG={"> Addmonths(Today(), -3)"}>} VDAF)

And why my expression (above) is not working?

I would like to show the last 3 months, compared with today's date.

How can I do it?

[Saravanan_Desingh]

Try sometingh like this

=avg({1 <STCK={'CVCB3'}, DVPG={"> $(Addmonths(Today(), -3))"}>} VDAF)
=avg({1 <STCK={'CVCB3'}, DVPG={"> $(=Addmonths(Today(), -3))"}>} VDAF)

[brunolelli87]

Hello,

Thanks for your help!

The first expression works, I mean, I can see the chart, but no matter if I use -3, -2, -1, or -12, the chart is the same. So it's not considering the time interval I'm looking for

=avg({1 <STCK={'CVCB3'}, DVPG={"> $(Addmonths(Today(), -3))"}>} VDAF)

[Saravanan_Desingh]

Can u bring the sub-expression into a variable and use it in your expression?

Let vDt = AddMonths(Today(), -3);
=avg({1 <STCK={'CVCB3'}, DVPG={"> $(vDt)"}>} VDAF)

[brunolelli87]

I don't think it's a good idea, because I don't have plans to reload the script it on a daily basis!

Any other idea?

[Lisa_P]

Hi Bruno,

You can create variables from the Sheet/Analysis view in Edit mode. There is a small icon bottom left in the assets panel where you can create and store variables for use within the app.

[Saravanan_Desingh]

=avg({1 <STCK={'CVCB3'}, DVPG={">$(=Date(Addmonths(Today(), -3),'MMDDYYYY'))"}>} VDAF)

Try to match with the Date format of DVPG. Also '=' is important here to refer it as Expression.