Daily Text Update, cannot return text value?

[derekjones]

Hi All

I'm sure this is an easy one, but I've hit a brick wall.

I'm trying to add a bit of fun to my sales dashboard by including a Daily "Quote of the Day" or "Joke of the Day" etc. I thought of storing the quotes in a table which has a different quote for every day (SHIPPED_DATE).

In order to find the latest day to bring back the latest quote of the day, I've created a variable called zMaxDate which finds the Max(SHIPPED_DATE) from the sales table.

My issue now comes trying to return the text for this date. I've tried using IF statements where zMaxDate = SHIPPED_DATE and also tried SetAnalysis, with no luck (I think it's because the SHIPPED_DATE could have more than one entry in the sales table, so a sum will work on data, but not on text?)

I've attached a simplified example of my problem.

Any suggestions welcomed,

My thanks in advance

Derek

[#2 · Accepted solution]

Hi,

try this:

only({$<SHIPPED_DATE={'$(zMaxDay)'} >}Day_Main)

Regards!!!

[#3]

If it's your own Quote spreadsheet, then try this.

Have only 2 columns in your spreadsheet, columns Quote and QuoteID

Create a variable such as vQuote = aggr(Quote, QuoteID = $(=max(QuoteID)))

Then have a text object which contains =$(vQuote).

Update the spreadsheet, whenever the doc reolads it will take the last quote in the list.

[derekjones]

Hi Gabriela

Thank you, that worked for my "test" example, however when I apply the same formula to my real full data set it's not working :0(

There's obviously something still different between the test file I've setup and my real data.

Hopefully this is at least a starting point of the correct formula and syntax I need, thanks again for your quick and accurate response.

Derek

[derekjones]

Thanks for the response, however I forgot to say, I want to preload a full month's quotes in advance so I don't need to do one each day, therefore picking the last quote will not solve my problem.

Derek

[derekjones]

For anyone interested..

The reason it did not work on my real data set is the zMaxDate variable in the real dataset was not date formatted i.e. zMaxDate = 40617 not 15/03/2011.

Once I changed varaibe to Date(Max(SHIPPED_DATE),'DD/MM/YYYY') it worked a treat,

KR

Derek

[#7]

Hi Derek,

Was reading the string on the discussion and thought immediately of the date/time format, which you figured out. It usually is an issue of UTC date vs D/M/Y or something of the like.

You can use Gabriella's only function solution and set analysis and use the today() function in the set analysis part if you are loading a month forward's worth (or more) of quotes.

Hope it helps.

[#8]

Using Gabriella's solution it would be:

=only({$<SHIPPED_DATE={$(=today())} >}Day_Main)

Btw, if you have problems in figuring out what set analysis is returning as a string, put the expression in a straight table with no label. Then when you hover your mouse over the expression on the table column header, QlikView gives you the whole string the set analysis is passing through. Much easier to resolve set analysis issues, especially with those date/time problems.

[derekjones]

Neat trick, thanks.