Subtotal by date

[dominic_marchan]

Hi all,

I am trying to do something that seems like a simple concept but can't seem to get around to finding a solution to actually do it.

I have a list of items that sell at different retailers. The sales are aggregated at the week level (shipment dates).

I have another sheet that tells me when the items went live on site at different retailers.

What I want to do is show a bar chart with the shipement date as dimension and show as measure the numbers of item (count) that were considered new for each shipment weeks

New item = if (shipment date - 12 weeks) >= min(date live on site) & not negative then item is new

I attached an excel file with mock data

[Lisa_P]

I would join the tables and use that as a data source (resident load) to calculate the Age of the products when shipped.
[Sales]:
LOAD
[Model],
[Name],
[Retailer],
[Product Subcategory],
[Product Type],
[Color],
[Date Shipped],
[Units Sold]
FROM [lib://Community/QlikMockData.xlsx]
(ooxml, embedded labels, table is Sales);

// [LiveOnSite]:
Left Join(Sales)
LOAD
[Retailer Name],
[Model],
[Verified live on site]
FROM [lib://Community/QlikMockData.xlsx]
(ooxml, embedded labels, table is LiveOnSite);

NewSales:
NoConcatenate
Load *,
[Verified live on site]-[Date Shipped] as Age
Resident Sales;

Drop Table Sales;

Then you can use Set Analysis to limit your bar chart to the age range you want
eg. Count({<Age={">-1<85"}>} [Units Sold])

[dominic_marchan]

Thank you somuch for taking the time, it is very appreciated.

I don't know if I did something wrong but the join seems to be creating duplicates

[Lisa_P · Accepted solution]

Yes, sorry, also should have merged the Retailer columns, so in the script, rename one or the other so they are the same:

eg  [Retailer Name] as [Retailer],

...

This should fix it

[dominic_marchan]

Works perfectly thank you so much!!!!