lookup based on dimension

[#1]

Hi,

I have a set of transaction data in a stock market. my goal is to find the latest transaction date in Persian (Jalali) format. since qlikview doesn't recognize the date format, I've bound Persian format to Gregorian one, and I'm trying to lookup Persian date with maximum Gregorian date for each customer as dimension. However my tries has been failed.

I've tried combination of only and max in set analysis but it failed since set analysis doesn't care about dimension...

I've also tried FirstSortedValue to no avail since it could be more than just one transaction in the last date.

I'd be glad if anyone knew a solution to the problem.

PS. I have attached a sample file...

[swuehl · Accepted solution]

You can try to use

FirstSortedValue(DISTINCT TransactionPersianDate,-TransactionDate)

This should be ok in your setting, since the persian date should not differ for the same max gregorian date, right?

[#3]

Yes, the Persian date does not differ for the same Gregorian date. But the problem is FirstSortedValue doesn't accept the DISTINCT parameter...

EDIT: It does accept the parameter but showing error.... weird!! ....

Thank you for your useful response..

[swuehl]

The syntax checker is not the most reliable part in QlikView.

There were some recent changes in the checker, in my V12 version, it's not marked as error.

But as a general rule, if in doubt, just press OK and check the result of the expression

[hic]

If you instead create your Persian dates as duals, you can use Max(TransactionPersianDate).

E.g.

Dual(TransactionPersianDate,TransactionDate) as TransactionPersianDate,

See also Non-Gregorian calendars

HIC