topic Re: Average with Set analysis in QlikView

Average with Set analysis

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Average with Set analysis

Lisa_P — Sun, 24 May 2020 22:57:43 GMT

Try this:

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

Re: Average with Set analysis

Vegar — Mon, 25 May 2020 05:01:40 GMT

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)

Re: Average with Set analysis

brunolelli87 — Mon, 25 May 2020 18:07:41 GMT

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?

Re: Average with Set analysis

Saravanan_Desingh — Mon, 25 May 2020 19:02:58 GMT

Try sometingh like this

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

Re: Average with Set analysis

brunolelli87 — Mon, 25 May 2020 19:10:59 GMT

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)

Re: Average with Set analysis

Saravanan_Desingh — Mon, 25 May 2020 19:26:39 GMT

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)

Re: Average with Set analysis

brunolelli87 — Mon, 25 May 2020 19:38:18 GMT

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?

Re: Average with Set analysis

Lisa_P — Mon, 25 May 2020 22:33:44 GMT

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.

Re: Average with Set analysis

Saravanan_Desingh — Mon, 25 May 2020 22:48:37 GMT

=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.

Re: Average with Set analysis

Saravanan_Desingh — Mon, 25 May 2020 23:13:54 GMT

One more to try:

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

Re: Average with Set analysis

Saravanan_Desingh — Mon, 25 May 2020 23:16:21 GMT

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

Re: Average with Set analysis

brunolelli87 — Tue, 26 May 2020 04:40:10 GMT

Thanks for all your assistance.

This one is working perfectly!

Re: Average with Set analysis

Saravanan_Desingh — Tue, 26 May 2020 13:42:33 GMT

you welcome