Expression/Variable help

[Anonymous]

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

[Colin-Albert]

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.

[jduarte12]

Hi

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

[Anonymous]

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

[Colin-Albert]

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

[snehamahaveer]

Hi Gareth,

May be you should try aggregating the expression.

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

Regards,

SM

[Colin-Albert · Accepted solution]

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.

[Anonymous]

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