How to sum value of sales per time period

[Dataflower]

Hi,

I am trying to build an application for evaluating sales of second hand cars (in my case it is about having data for three complete years 2018, 2019 and 2020).

One of the goals to be achieved by this application is to find out whether individual Sales Reps achieved their planned sales goals (for example in Jan 2019).

As I know the exact date of sales from Sales Invoice Date, it shouldn't be difficult to sum up real sales for any given month and year (for example Jan 2019) based on the sales invoice date.

However my SUM statement is not working the way I would want to.

sum({${<TimePeriod>}} SalesInvoice_Amount)

Here is the clipping of the pivot table I'm trying to build.

Below is shown how the table with car sales data is structured:

//Loading details about car purchases and sales
CarBusinessDetails:
LOAD
[car_VIN],
[purchased_price]
FROM [lib://hiring/PurchasedPrice.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);

Left Join(CarBusinessDetails)
LOAD
[car_VIN] AS [car_VIN],
[id_invoice] as [SalesInvoice_ID],
[customer_name],
[customer_last_name],
[customer_name]&' '&[customer_last_name] as [CustomerFullName],
[customer_country],
[customer_city],
[customer_gender],
[car_make],
[car_model],
[car_color],
[invoice_date],
Date([invoice_date], 'DD/MM/YYYY') as [SalesInvoiceDate],
Month([invoice_date]) as [SalesInvoiceMonth],
Year([invoice_date]) as [SalesInvoiceYear],
(Month([invoice_date]))&' '&Year([invoice_date]) as [TimePeriod],
[invoice_amount] as [SalesInvoice_Amount],
[id_salesperson] as [SalesPersonID]
FROM [lib://hiring/SalesData.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

// Performing basic calculations
CompleteCarBusinessDetails:
Load *,
([SalesInvoice_Amount] - [purchased_price]) as [Margin],
Round(([SalesInvoice_Amount]/([purchased_price]/100)-100), 0.001) as [Margin%]
Resident CarBusinessDetails;
Drop Table CarBusinessDetails;

This is it. Thank you in advance for your suggestions.

DF

[Or]

What, exactly, are you trying to do here? Generally speaking, a simple sum statement such as Sum(SalesInvoice_Amount) should work no matter which dimensions you place in the table, as long as these dimensions are properly connected to the SalesInvoice table in the schema, so based on your description you should need to use that with dimensions of SalesInvoiceMonth and SalesInvoiceYear.

[Dataflower · Accepted solution]

Hello everyone,

at the end it was about using Pick and Match function.

I succeded in building my pivot table only thanks to Manishkumar Kachhia and his video tutorial "Qlik_027: Create a customised report in Qlik Sense Pivot Table using Pick and Match functions."

The video can be found using following link: www.youtube.com/watch?v=Pxmw3ddYodw

Perhaps it can help to someone else as well.

DF