topic Re: Last date with data formula required in QlikView

Last date with data formula required

Wed, 22 May 2013 14:26:46 GMT

Hi,

I want to create a variable which is the date at which a particular product ends.

For example if the sale of Product XYZ ended in Dec 2012 while the financial year ends in April 2013, what function could I use?

thanks in advance

Re: Last date with data formula required

Gysbert_Wassenaar — Wed, 22 May 2013 14:52:35 GMT

Try: max({1<Product={'XYZ'}>} MyDate)

Re: Last date with data formula required

Wed, 22 May 2013 15:59:47 GMT

Thanks Gysbert.

This is kind of what I need however, the month Jan to April have zero's in them therefore the mas date being picked up is still April 2013 not Dec 2012.

I know I need to use set analysis in some way but not sure of the syntax.

=max({sum of the data is <> 0} Date)

Re: Last date with data formula required

Gysbert_Wassenaar — Wed, 22 May 2013 16:27:31 GMT

max({1<Product={'XYZ'},SalesAmount-= {0}>} MyDate). The -= is correct even though the expression editor flags it as wrong with a red line. Change SalesAmount with the name of the field that has the 0 values.