Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a question regarding a pivot chart and its calculation in expressions. I've created a chart (pivot table) with dimension the customer number and expression the sales of customers. So far so good. I select dates, for instance year 2013 and I get the sales per customer number in the report. On the other hand, I have an excel file with three columns: customer number, Date_from and Date_to. These customers need to be joined to my QV file and the report should calculate sales only for the mentioned period in the excel file, ignoring the selections in the QV doc. The rest of the customers will be calculated based on dates in the QV doc, but those who are included in the excel file will show sales based on dates in excel. I loaded the excel file in QV doc and joined it with the rest of the customer (using Customer number as key). However, how can I show the sales for customers for specific dates and the rest of the customers for the filtered dates?
Any kind of assistance in much appreciated.
Thanks,
You can load the excel data in a separate qvd and append the same with existing data in your qvw with proper date format.
Please upload some sample data and your expected result for better understanding.
Selva
add a new field in your excel file say "Filter"or " specific" etcc.
You can use that field in your set analysis.
Thanks Selva & Jebamalai, Here is the attachment, i've created to make it clear. I've used set analysis, you can see it in the report.
I've loaded from table sales.xls the sales per customer and invoice date. Then i've loaded customer number and specific dates from the second excel "KAM_Flags_Copy.xlsx". All I need is customers included in the second table present sales according to specific dates. Is it possible?
Is this what you are expecting...
Your Second table may look like this
Cust No. | Invoice_date | SecondTableDate | sum(Sales) | SpecificDateSales |
1001 | 1/1/2010 | 1/1/2010 | 500 | 450 |
1001 | 10/11/2010 | 694 | 0 | |
1001 | 1/11/2013 | 594 | 0 | |
1001 | 6/2/2014 | 6/2/2014 | 300 | 250 |
1002 | 2/2/2010 | 600 | 0 | |
1002 | 3/10/2010 | 200 | 0 | |
1002 | 2/10/2013 | 100 | 0 | |
1002 | 11/12/2013 | 128 | 0 | |
1003 | 3/1/2010 | 3/1/2010 | 521 | 450 |
and your first table...
Cust No. | Invoice_date | sum(Sales) | Sales from specific dates |
1001 | 1/1/2010 | 500 | 0 |
1001 | 10/11/2010 | 694 | 0 |
1001 | 1/11/2013 | 594 | 0 |
1001 | 6/2/2014 | 300 | 0 |
1002 | 2/2/2010 | 600 | 0 |
1002 | 3/10/2010 | 200 | 0 |
1002 | 2/10/2013 | 100 | 0 |
1002 | 11/12/2013 | 128 | 0 |
1003 | 3/1/2010 | 521 | 0 |
You can load like this:
FirstTable:
LOAD [Cust No.]&'#'&Invoice_date as Key,
[Cust No.],
Invoice_date,
[sum(Sales)]
//[Sales from specific dates]
FROM
TestDel.xlsx
(ooxml, embedded labels, table is Sheet1);
SecondTable:
LOAD [Cust No.]&'#'&Invoice_date as Key,
SecondTableDate,
//[sum(Sales)],
SpecificDateSales
FROM
TestDel.xlsx
(ooxml, embedded labels, table is Sheet2);
and in dimension you can mention like this...
Dimension 1: [Cust No.]
Dimension 2: if(Invoice_date=SecondTableDate,Invoice_date)
Expression:
Sum(SpecificDateSales)
Output:
Cust No. | InvoiceDate | sum(SpecificDateSales) |
1001 | 1/1/2010 | 450 |
1001 | 6/2/2014 | 250 |
1003 | 3/1/2010 | 450 |
Hi Selva,
Well, actually, if the customer number is included in the second table the sales should be calculated for the time period "Cust_date_from" and "Cust_date_to" and ignore completely the invoice_date from the first table.
But if the customer number is not included in the second table, then sales should be calculated based on invoice_date from the first table. Any suggestions?