Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brunolelli87
Creator II
Creator II

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

1 Solution

Accepted Solutions
Saravanan_Desingh

One more to try:

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

View solution in original post

13 Replies
Lisa_P
Employee
Employee

Try this:

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

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Employee
Employee

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.