topic Re: Assistance with dates in QlikView

Assistance with dates

Mon, 23 Jun 2014 13:05:20 GMT

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,

Re: Assistance with dates

selvakumarsr — Mon, 23 Jun 2014 13:19:39 GMT

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

Re: Assistance with dates

Anonymous — Mon, 23 Jun 2014 13:29:27 GMT

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

You can use that field in your set analysis.

Re: Assistance with dates

Mon, 23 Jun 2014 15:21:08 GMT

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?

Re: Assistance with dates

selvakumarsr — Tue, 24 Jun 2014 05:25:16 GMT

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

Re: Assistance with dates

Tue, 24 Jun 2014 09:15:02 GMT

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?