Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
dominic_marchan
Contributor III
Contributor III

Subtotal by date

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

Labels (3)
1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

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

View solution in original post

4 Replies
Lisa_P
Employee
Employee

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
Contributor III
Contributor III
Author

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
Employee
Employee

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

View solution in original post

dominic_marchan
Contributor III
Contributor III
Author

Works perfectly thank you so much!!!!