Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a question regarding the following conditions:
I have a table:
Letter | Apr-14 | May-14 | Jun-14 | Jul-14 | Aug-14 | Sep-14 |
---|---|---|---|---|---|---|
A | 100 | 200 | 300 | 400 | 500 | 600 |
B | 100 | 200 | 300 | 400 | 500 | |
C | 600 | 700 | 800 | 900 |
I need to create a table where I have to output the latest value (latest value is base on Letter (A<B<C<D,.....)
The output should be:
Apr-14 | May-14 | Jun-14 | Jul-14 | Aug-14 | Sep-14 | |
---|---|---|---|---|---|---|
Qty | 100 | 100 | 600 | 700 | 800 | 900 |
How do I achieve this? Please take note that my selection of letters can differ. I select A and C it should output something like this:
Apr-14 | May-14 | Jun-14 | Jul-14 | Aug-14 | Sep-14 | |
---|---|---|---|---|---|---|
Qty | 100 | 200 | 600 | 700 | 800 | 900 |
The month selected also changes. How to achieve this? How can I only get the latest value?
Thanks in advance!
Use pivot table
Expression firstsortedvalue(Letter,-Date)
Caluclated Dimension (Date &' [' & Qty & ']')
Please do take note that those are not just numbers. they are computed values. Sum(QTY)
maybe
subfield(concat(if(len(trim([Sep-14]))>0,[Sep-14]),'@', -match(Letter, 'A','B','C')), '@')
Hi thanks for the reply. please take note that I only have one field for the month. Not separated months
Hi Lyn,
Look at my reply in your other similar thread.
Thanks,
Ram
Hi Ram,
Thanks for the reply! how about if I have a computation inside the formula?
for example: sum(Qty)
Thanks!
Look at my latest attachment. Mark mine as the correct answer if you're happy.
Hi,
Try like this
Temp:
CrossTable(Month, Qty)
load * inline
[
Letter, Apr-14, May-14, Jun-14, Jul-14, Aug-14, Sep-14
A ,100 ,200, 300, 400, 500, 600
B ,0 ,0,100, 200 ,300 ,400 ,500
C ,0,0 ,600 ,700, 800, 2500
];
Data:
Load
*,
If(Previous(Month) <> Month OR RowNo() = 1, 1, 0) AS IsLatest
RESIDENT Temp
WHERE Qty > 0
Order by Month, Letter Desc;
Drop table Temp;
Use this as expression in chart
Sum({<IsLatest={1}>} Qty)
Regards,
Jagan.