
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Average with Set analysis
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
One more to try:
=avg({1 <STCK={'CVCB3'}, DVPG={">$(=Date(Addmonths(Today(), -3)))"}>} VDAF)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
=avg({1 <STCK={'CVCB3'}, DVPG={">03/01/2020"}>} VDAF)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try sometingh like this
=avg({1 <STCK={'CVCB3'}, DVPG={"> $(Addmonths(Today(), -3))"}>} VDAF)
=avg({1 <STCK={'CVCB3'}, DVPG={"> $(=Addmonths(Today(), -3))"}>} VDAF)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=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.

- « Previous Replies
-
- 1
- 2
- Next Replies »