Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data as below in qvw. I need the pivot table should show only values which have Maximum FirstNumber. corresponding values to the Maximum FirstNumber
please see attached sample Input data.
I need out put as shown in attached Output.
Thanks.
Try this as your expression:
=Only({<FirstNumber = {"$(=Max(FirstNumber))"}>}Sales)
With all other fields as dimension. Output looks like this:
Thanks. How can I use the same in pivot table?. when I use the expression in pivot tale it is showing blank chart.
Please let me know.
Have you looked at the attached qvw? It is a pivot chart and it is not showing blank. I am not sure what exactly you are doing. Can you explain a little more?
Best,
Sunny
Thanks. Sorry I have send this earlier. I need pivot table as in attached
can you let me know how to use your expression in this pivot table please.
Not sure what your requirement is, but when I put the formula I gave you, I do see values showing up in the places they are supposed to show up and showing blanks where there are not values. What are you expecting to see?
for Date = 05/05/2015 there are 6 rows with FirstNumber 0,1,2,3,4,5.
MonthYear | Total | Total | May 2015 | May 2015 | |
CAPP | Region | Sales | Total | Sales | Total |
63287325 03/09/2010 | Calcutta | 400 | 400 | 400 | 400 |
63287325 04/09/2010 | Calcutta | 200 | 200 | 200 | 200 |
63287325 05/09/2010 | Calcutta | 400 | 400 | 400 | 400 |
63287325 06/09/2010 | Calcutta | 400 | 400 | 400 | 400 |
63287325 07/09/2010 | Calcutta | 400 | 400 | 400 | 400 |
63287325 08/09/2010 | Calcutta | 216.5 | 216.5 | 216.5 | 216.5 |
But I need the pivot table should show only one row with Maximum FirstNumber as below
MonthYear | Total | Total | May 2015 | May 2015 | |
CAPP | Region | Sales | Total | Sales | Total |
63287325 08/09/2010 | Calcutta | 216.5 | 216.5 | 216.5 | 216.5 |
I need
Dimension = CAPP
Dimension = Region
Dimension = MothYear
Expression1= Sales
Expression 2 = Sum(Sales)
would it be possible.?
Please help me.
Expression 1:
=Only({<FirstNumber = {"$(=Max(FirstNumber))"}>}Sales)
when I select 63287325 08/09/2010 I see exactly what you wanted:
I am attaching the application for you to review also (PFA)
Best,
Sunny
can you let me know if there are more ID instead of one. how can I get this. please see attached.
Check this out: (PFA)
I just worked on Sales Expression. Not sure what you are trying to do in Total.
Best,
Sunny