topic Re: Expression help in QlikView

Expression help

Mon, 14 Dec 2015 11:27:03 GMT

Ref YearMonth Receipt

222 2015 -11 0

333 2015 - 12 0

231 2015 - 12 1

156 2015 - 12 1

Please can you give me a FIXED expression for:

COUNT of 'REF'

WHERE 'YearMonth' is current

and 'Receipt' = 1

Re: Expression help

tresB — Mon, 14 Dec 2015 11:30:13 GMT

Try:

Count({<Receipt={1}, YearMonth={'$(=Year(Today())&' - '&Num(Month(today())))'}>}REF)

Note: Space before and after '-', you have to be careful about.

Re: Expression help

sunny_talwar — Mon, 14 Dec 2015 11:30:40 GMT

Not sure if you YearMonth field is a date field and how this is getting created in the script, but if it is truely a date field, something like this should work;

Count({<Receipt = {1}, YearMonth = {"$(=Date(Max(YearMonth), 'YYYY - MM'))"}>}REF)

Re: Expression help

Anonymous — Mon, 14 Dec 2015 11:34:16 GMT

may be this:

=Count({<Receipt={'1'}, YearMonth={'$(=max(num(YearMonth))'}>} REF)

Re: Expression help

Mon, 14 Dec 2015 11:42:23 GMT

Unfortunately this has not given me a result as I have records for the current month and nothing is displayed.

Re: Expression help

sunny_talwar — Mon, 14 Dec 2015 11:44:34 GMT

I think the problem might be related to how the YearMonth field is getting calculated in the script. Are you using a function like this?

Date(DateField, 'YYYY - MM') as YearMonth?

Re: Expression help

Mon, 14 Dec 2015 11:45:13 GMT

Hi Sunny

Expression states ok but no result - so this must be the way I have loaded the date.

My YearMonth field in load is:

CreatedOn, (comes in as a date time stamp from SQL)

Convert(varchar,year([CreatedOn])) + '-' + convert(varchar,month([CreatedOn])) as YearMonth,

Sorry very new to this!

Re: Expression help

Mon, 14 Dec 2015 11:45:49 GMT

Hi Balraj

Please see my reply to Sunny.....

Still having errors, probably due to date format ?

Thanks

Re: Expression help

rubenmarin — Mon, 14 Dec 2015 11:47:59 GMT

Hi Emma, tresesco expression seems ok, can you ckeck the case sensitivity?. I.e. 'Ref' instead of 'REF'.

Also confirm the YearMonth format, there is an space before and after the dash ( - )?

Re: Expression help

sunny_talwar — Mon, 14 Dec 2015 11:49:13 GMT

Not an expert in SQL, but this seems like text instead of date:

May be try this:

LOAD Date(Date#(YearMonth, 'YYYY-MM'), 'YYYY-MM') as YearMonth;

SELECT SQL

CreatedOn, (comes in as a date time stamp from SQL)

Convert(varchar,year([CreatedOn])) + '-' + convert(varchar,month([CreatedOn])) as YearMonth,

and then this expression:

Count({<Receipt = {1}, YearMonth = {"$(=Date(Max(YearMonth), 'YYYY-MM'))"}>}REF)

Re: Expression help

marcus_sommer — Mon, 14 Dec 2015 12:02:00 GMT

In this case should the suggestion from tresesco work if you removed the additionally spaces within ' - '. In general it's better to use always real dates in a pure numeric format for all matching and calculations. It enabled for example to compare such YearMonth field with >= or <= which isn't possible with a string like in your sql. Often it is helpful to use a master-calendar for this: How to use - Master-Calendar and Date-Values.

- Marcus

Re: Expression help

puttemans — Mon, 14 Dec 2015 13:36:44 GMT

Hi Emma,

Try to execute the formula without date-parameters. If it works, you know it is the data, if not, it may be other things as well.

Count({<Receipt = {1}}>}REF)

Regards,

Johan