topic Re: Function within Set Analysis - help needed! in QlikView

Function within Set Analysis - help needed!

Mon, 14 Apr 2014 13:24:28 GMT

Hi All,

I am looking to set up a straigfht table which will display the time logged to a system on a rolling 5 month basis.

The field which contains the time period is called 'YearMonth'. It is a dual field so it has a numeric value (to allow sorting etc) and a text representation e.g. '2014-Mar'.

I would like to use a function within a Set Analysis statement to do this, however the below set analysis is not functioning as required. I need the Time field to be summed where the YearMonth field is equal to the maximum YearMonth value.

Sum ( {<YearMonth = {"$(=max( {1} YearMonth))" }>} Time)

More specifically the issue can be narrowed to this bit {"$(=max( {1} YearMonth))" }>}.

A zero value is returned.

There must be a syntax error which is not being flagged by the checker. Can anyone shed any light?

Apologies but due to company policy I cannot share the app.

Thanks,

Re: Function within Set Analysis - help needed!

jerem1234 — Mon, 14 Apr 2014 13:30:18 GMT

It's because the max function converts the YearMonth into a number and you need the text representation for set analysis. Therefore you need to convert your number back into the equivalent text representation of YearMonth. You can try with date like:

{"$(=date(max( {1} YearMonth), 'YYYY-MMM'))" }

or use another function to get the text representation.

Hope this helps!

Re: Function within Set Analysis - help needed!

Mon, 14 Apr 2014 13:33:19 GMT

Try

Sum ( {<YearMonth = {'$(=max(YearMonth))' }>} Time)

like I am suing in my application as below..

sum({<SalesMonthYear= {'$(=Max(SalesMonthYear))'}>}Net_Trading_Days)

Re: Function within Set Analysis - help needed!

Mon, 14 Apr 2014 13:41:39 GMT

D'oh!

Thanks very much.