Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have a sample data below of what I'm currenty viewing & what I would like to view.
Current
Customer # | Customer Name | ItemlookupCode | Discount | SaleStartDate | SaleEndDate |
---|---|---|---|---|---|
1010 | AA | 9361 | 5% | 1/07/2011 | 30/06/2012 |
1010 | AA | 9361 | 5% | 1/07/2012 | 31/12/2012 |
1010 | AA | 9361 | 3% | 1/1/2013 | 31/12/2013 |
1111 | CC | 6912 | 19% | 1/07/2011 | 31/12/2012 |
1111 | CC | 6912 | 12% | 1/1/2013 | 01/03/2013 |
This is what I need, basically the latest saleenddate is all I'm interested in to see what is the latest sale for a Item.
Not sure what type of expression will produce this output for me
Customer # | Customer Name | ItemlookupCode | Discount | SaleStartDate | SaleEndDate |
---|---|---|---|---|---|
1010 | AA | 9361 | 3% | 1/1/2013 | 31/12/2013 |
1111 | cc | 6912 | 12% | 1/01/2013 | 01/03/2013 |
please use dimension limit avaialble in QV 11+ . it's in built feature .
try to use firstsorted value.
try this...
Num(Sum({<SalesStartDate={$(=Max(Year))} >}Discount)'###.%')
Thanks, but I'm unfortunately I'm using v10
Sorry, How would I do that?
Ok, so should this be a calculated dimension or expression?
also '###.%' what is that supposed to represent?
as ur measure is discount and discount is showing (%)..
syntax...num( expression [ , format-code [ , decimal-sep [ , thousands-sep ] ] ] )
The num function formats the expression numerically according to the string
given as format-code. Decimal separator
and thousands separator can be set as third and fourth parameters. If the
parameters 2-4 are omitted, the number format set in the operating system is
used.
.. '###.%' this format will help u data with %symbol in ur chart
see attachement
or see this code
in script--
DATA:
LOAD
[Customer #],
[Customer Name],
ItemlookupCode,
Discount,
date(date#(SaleStartDate,'DD/MM/YYYY'),'DD/MM/YYYY') AS Sale_StartDate ,
date(date#(SaleEndDate,'DD/MM/YYYY'),'DD/MM/YYYY') AS Sale_EndDate;
LOAD * Inline [
Customer #, Customer Name, ItemlookupCode, Discount,SaleStartDate, SaleEndDate
1010, AA, 9361, 5%, 1/07/2011, 30/06/2012
1010, AA, 9361, 5%, 1/07/2012, 31/12/2012
1010, AA, 9361, 3%, 1/1/2013, 31/12/2013
1111, CC, 6912, 19%, 1/07/2011, 31/12/2012
1111, CC, 6912, 12%, 1/1/2013, 01/03/2013
];
OUTPUT:
LOAD
[Customer #],
[Customer Name],
ItemlookupCode,
date(max(Sale_StartDate)) as max_Sale_StartDate,
date(max(Sale_EndDate)) as max_Sale_EndDate
Resident DATA
Group By [Customer #],[Customer Name],ItemlookupCode;
***********************************************
in pivot--
dimension1- [Customer #],
dimension2- [Customer Name],
dimension3----ItemlookupCode,
expression 1- FirstSortedValue(Discount,-Sale_EndDate)
expression2--max_Sale_StartDate
expression3--max_Sale_EndDate
then output like this
Customer # | Customer Name | ItemlookupCode | Discount | max_Sale_StartDate | max_Sale_EndDate |
1010 | AA | 9361 | 3% | 1/1/2013 | 12/31/2013 |
1111 | CC | 6912 | 12% | 1/1/2013 | 3/1/2013 |
See the attached file
and output like this
Customer # | Customer Name | Item | Discount | Sale Start Date | Sale End Date |
1010 | AA | 9361 | 3% | 01/01/2013 | 31/12/2013 |
1111 | CC | 6912 | 12% | 01/01/2013 | 01/03/2013 |