Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I want a min(date) out of 4 max(date). Below is the scenario:
Key_Cat | Max(Latest_Date) | |
---|---|---|
Auto Loans |
| |
Deposit | 25-Jun-18 | |
Mortgage |
| |
Share Financing |
|
I want to show 30-May-18 as final date in some other table.
Deposit and Secured Loans | Data as of |
---|---|
AutoLoans.A1 | 30-May-18 |
AutoLoans.A2 | |
Deposit.D1 | |
Mortgage.M1 | |
ShareFinancing.S1 | |
Some other section | |
I have applied below formula, but that doesn't give me required date i.e. 30-May-18.
=date(min(aggr(max(Latest_date),Key_Cat)))
Please help me. Thanks in advance!
Message was edited by: Amruta Joshi Sorry about confusion. Added sample Data and expected output. Thank You!
You need to use the Total qualifier to calculate across dimensions in you chart.
Try. date(min(total <Key_Cat> aggr(max(Latest_date),Key_Cat)))
Your sample data is confusing. Can you provide some more data?
May be:
=date(Min(Max(Date))) as [Data as of]
Have you tried your expression: =date(min(aggr(max(Latest_date),Key_Cat))) in a textbox? That should give you the expected output. Could you share your sample qvw?
Sorry could not share the whole application. But I'm getting correct result in textbox, not in table.
What are the dimensions you are using in the chart? Could you share a screenshot?
I'm creating straight table shown in Excel sheet 2. Dimension are Key_Cat, Sub_Cat, Latest_date
You need to use the Total qualifier to calculate across dimensions in you chart.
Try. date(min(total <Key_Cat> aggr(max(Latest_date),Key_Cat)))
Try like:
=date(min(Total aggr( max(Latest_date),Key_Cat)))
Or,
=date(min(Total aggr( NODistinct max(Latest_date),Key_Cat)))
Thank you again !! Total works great.