Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Assistance with dates

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,

5 Replies
selvakumarsr
Creator
Creator

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

Anonymous
Not applicable
Author

add a new field in your excel file say "Filter"or " specific" etcc.

You can use that field in your set analysis.

Not applicable
Author

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?

selvakumarsr
Creator
Creator

Is this what you are expecting...

Your Second table may look like this

Cust No.Invoice_dateSecondTableDatesum(Sales)SpecificDateSales
10011/1/20101/1/2010500450
100110/11/2010 6940
10011/11/2013 5940
10016/2/20146/2/2014300250
10022/2/2010 6000
10023/10/2010 2000
10022/10/2013 1000
100211/12/2013 1280
10033/1/20103/1/2010521450

and your first table...

Cust No.Invoice_datesum(Sales)Sales from specific dates
10011/1/20105000
100110/11/20106940
10011/11/20135940
10016/2/20143000
10022/2/20106000
10023/10/20102000
10022/10/20131000
100211/12/20131280
10033/1/20105210

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.InvoiceDatesum(SpecificDateSales)
10011/1/2010450
10016/2/2014250
10033/1/2010450
Not applicable
Author

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?