topic Re: Help using first sorted value in QlikView

Help using first sorted value

ali_hijazi — Thu, 11 Aug 2016 18:56:30 GMT

Hello got a fact table with customers and date of transaction

I need to create a pivot table with dimension month_year and customer key

What I want to display is the max date for each customer before the month year value in the dimension

Re: Help using first sorted value

sunny_talwar — Thu, 11 Aug 2016 18:59:52 GMT

Max date? or value associated with max date?

For Max date: Max(DateField)

For value associated with max date: FirstSortedValue(Value, -Date)

Re: Help using first sorted value

vishsaggi — Thu, 11 Aug 2016 19:03:37 GMT

May be this: Change the fields accordingly.

= FirstSortedValue(Datefield, -Aggr(Max(Date(DateField, 'DD-MM-YYYY'), Customer))

Re: Help using first sorted value

ali_hijazi — Thu, 11 Aug 2016 19:18:35 GMT

Where in this expression are you specifying to get the dates prior to date value of dimension?

Re: Help using first sorted value

neelamsaroha157 — Thu, 11 Aug 2016 19:37:13 GMT

You can also create a table in script with the Month_Year and customer as key and Min of Date field aggregated by the key.

This way you will get Min of Date as 1 record per Year_Month_Customer combination.

Re: Help using first sorted value

swuehl — Thu, 11 Aug 2016 19:41:21 GMT

"What I want to display is the max date for each customer before the month year value in the dimension"

Not sure I understand what you want to achieve.

Do you want to display a value in the dimension section of your table, left of the month year dimension?

Or do you want to create an expression that calculated the max(Date) before the month year per customer (so, for the previous month with regard to the current year month dimension value)?

Maybe some sample data and description of your expected result may help to understand.

Re: Help using first sorted value

ali_hijazi — Thu, 11 Aug 2016 19:51:24 GMT

Check the sample document I supplied in the following thread

Help in expression FOR LOST CUSTOMERS

Yes I want for each month year and customer on pivot table to get the last time that customer had a sales transaction I.e if I select June 2016 customer c1

Then next to June 2016 c1 display for example June 2015 if June 2015 was the last time this customer was invoiced before June 2016