Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
Contributor II

## Sum Values based on Unique Values in Another Field

I've got two tables in my data model:

• Sales data by location, product, and fiscal month
 Fiscal Month Location ID Prod ID Sales 201802 1 1 20 201803 1 1 0 201804 1 1 55 201805 1 1 14 201806 1 1 28 201807 1 1 0 201808 1 1 0 201809 1 1 38 201810 1 1 0 201811 1 1 0 201812 1 1 12 201901 1 1 11 201802 2 1 0 201803 2 1 0 201804 2 1 1 201805 2 1 2 201806 2 1 9 201807 2 1 0 201808 2 1 0 201809 2 1 0 201810 2 1 4 201811 2 1 0 201812 2 1 4 201901 2 1 6
• Fiscal month and week count
 Fiscal Month Week Count 201802 4 201803 5 201804 4 201805 4 201806 5 201807 4 201808 4 201809 5 201810 4 201811 4 201812 5 201901 4

My goal is to find the average weekly sales, based on the time-period selected by my users. I get a number that’s too low because it’s adding up the Week Count for all rows in the data model when I use

Sum(Sales) / Sum(Week Count)  = 1.96 units/week

What I need to end up with is the sum of the Week Count for each unique month in the selected time-period, or if no time is selected, then for the entire period in the app (3.92 units/week).

1 Solution

Accepted Solutions
MVP

Do you join the two tables together into a single table in your app? Try this may be

```Sum(Sales)/
Sum(Aggr(Sum(DISTINCT [Week Count]), [Fiscal Month]))```
2 Replies
MVP

Do you join the two tables together into a single table in your app? Try this may be

```Sum(Sales)/
Sum(Aggr(Sum(DISTINCT [Week Count]), [Fiscal Month]))```
Partner - Contributor III

Please find the attached qvw.

The same syntax is working for me. Please let me know if you any query.

Thanks

Nilaksh Mahajan