Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
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.