topic Re: Expression/Variable help in QlikView

Expression/Variable help

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Good morning folks,

If I have an expression, Max( [Invoice Date]), that I have saved as a variable called MaxInv.

However, when I include the variable in the following expression, it's not returning the desired result.

= Count( IF( '$(eMininv)' >= '$(vMaxDate)' AND '$(eMinInv)' <= '$(vMaxDate)', [Supplier Name]))

Any ideas?

Many thanks,

Gareth

Re: Expression/Variable help

Colin-Albert — Mon, 02 Oct 2017 10:25:25 GMT

Hi Gareth,

When you say "it is not returning the desired result", is it returning an incorrect count or nothing?

You have spelt eMinInv differently in your expression - QlikView is case sensitive eMininv and eMinInv are two different variables to Qlik.

Your max date variable is named MaxInv but this is not used in the expression you posted.

If you add your expression to a straight table chart with a blank label, you can hover over the column header and see how Qlik is evaluating your expression.

Re: Expression/Variable help

jduarte12 — Mon, 02 Oct 2017 10:33:53 GMT

Also check if the date formats are compatible in those variables.

Re: Expression/Variable help

Anonymous — Mon, 02 Oct 2017 10:35:38 GMT

Hi Colin,

Thanks for your reply.

I have amended the spelling errors, and it is now returning zero rather than 23,000! The expression should return 763.

I have attached my file if this helps evaluate where I am going wrong.

Thanks,

Gareth

Re: Expression/Variable help

Colin-Albert — Mon, 02 Oct 2017 11:11:28 GMT

Your expression should be

= Count(distinct IF( [Invoice Date] >= '$(vMinDate)' AND [Invoice Date] <= '$(vMaxDate)', [Supplier Name]))

Or using Set Analysis

= Count({<[Invoice Date] = {">= $(vMinDate)<=$(vMaxDate)"}>} distinct [Supplier Name])

These return 1752 using your data

Re: Expression/Variable help

snehamahaveer — Mon, 02 Oct 2017 11:12:00 GMT

Hi Gareth,

May be you should try aggregating the expression.

=Sum(aggr(if($(eMinInv) >= (vMaxDate) AND $(eMinInv) <= (vMaxDate), 1,0) [Supplier Name]))

Regards,

Re: Expression/Variable help

Colin-Albert — Mon, 02 Oct 2017 11:37:54 GMT

Try this expression which returns 763

= sum(aggr(

if( max({1} [Invoice Date]) >= date(floor(monthstart(makedate(min(Year), min(match(Month, 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))))))

and max({1} [Invoice Date]) <= date(floor(monthend (makedate(max(Year), max(match(Month, 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))))))

, 1),

[Supplier Number]))

The expression would be simpler if your Month field was held as a correctly formatted dual field then the match() expression would not be needed to get the correct min & max values.

Use the QlikView Month() functions to derive month from dates rather than using text fields.

Re: Expression/Variable help

Anonymous — Mon, 02 Oct 2017 13:45:22 GMT

Thanks Colin,

That works exactly as I wanted.

For reference, how would I correctly format my Month field as a dual field, and then how would this change the expression?

Thanks,

Gareth