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
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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]))

View solution in original post

nilaksh92
Partner
Partner

Please find the attached qvw.

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

 

Thanks 

Nilaksh Mahajan