select in an expression or variable

[#1]

sorry for my english.

I need a way to make a select x from table in an expression field or variable. Is it possible.

My problem is that i have a items price list with "start date" and "end date".

I made an input box where the user insert a date and in a tab pivot he wants the list of items that has validate price in that date.

How can i make this work?

tnx

db

[IAMDV]

db - This is possible by using the variables and using the same variable in the expression. Please can you share the sample data... I can give a go 🙂

Cheers - DV

[#3]

Tnx,

i send you my file.

if you insert 01-04-2011 into "data riferimento" i'd like that into the table remains only the rows with datainizio most near to the datariferimento (es. 01-03-2011).

tnx a lot.

db

[#4]

nobody can help me?

[#5]

Hi d_borghi,

Am relatively new to QV, so there maybe a better solution than the one attached....

--------------------------------------------------------------------------------------------------------

Changed the input box number format, to date

Created/tested text box to return the correct value from your input box/ASK_DATA variable

Created new calculated dimensions for each of your reported dimensions.

For a field value to appear in your report, it first has to pass the following evaluation:

if DATAINIZIO >= ASK_DATA , then return field value, else null :

=if(DATAINIZIO>='$(ASK_DATA)',ARTICOLO,null())

For the date fields, remember to add the "date" syntax as follows:
=if(DATAINIZIO>='$(ASK_DATA)',date(DATAFINE,dd/mm/yyyy),null())

If you don't want to see the last row (null) with the value, in the dimensions tab, deselect "display when value is null"

Is this what you were hoping to achieve?

Kind regards,

Rich

[#6]

Hi D,

Slightly revised file, which perhaps fits better with your requirement...

2 date input boxes required, 1 for start date other for the end date.

Calculated dimensions' formula changes slightly to take into account the end date as well:

=if(DATAINIZIO>='$(StartDate)' and DATAFINE<='$(EndDate)',ARTICOLO,null())

Kind regards,

Rich

[IAMDV]

db - I am sorry I could not work on this one. I am little busy with my work. However, before I proceed any further on this one. I wanted to understand the issue completely.

RichSheppard already provided a solution which I assume is what you need...

However, I am working on using set analysis in your expression and not touching the dimensions. Please try to use this expression to search on manually entered date through a variable.

=COUNT({<DATAINIZIO = {'$(ASK_DATA)'}>} PREZZO)

And my understanding is that you need to get nearest (less than the current date) if there is no date available matching the manually entered date. Is this right?

Cheers - DV