Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt_Jester
Contributor II
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
sunny_talwar

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]))

View solution in original post

2 Replies
sunny_talwar

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]))
nilaksh92
Partner - Contributor III
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