Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Straight Table calculation

HI All

I am currently using a straight table for calculating the sum(sales) between start date and end date

my dimensions are branch, start date and end date and sales date

sales date is always between start date and end date

my expression is sum(sales)  for sales and sum(Daily_Targets)

it is summing up the sales correctly between start date and end date

but when i use  sum(Daily_Targets) it is not summing up the Daily_Targets between the start date and end date

Please suggest

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You can also start with a concatenated sales and target table, like shown in attached sample.

You will notice that you split your weekly targets into 5 parts, but you will have sales on dates that are not part of your start-end intervals.

(which you lost in your previous sample).

View solution in original post

9 Replies
swuehl
MVP
MVP

How does your data model look like?

It's hard to answer your question with the information provided. It would be best if you can upload a small sample QVW.

Not applicable
Author

HI swuehl

when i try to create a small sample my conditions are working correctly

but not working in my actual data

here i a sample exactly like my actual data

Anonymous
Not applicable
Author

Sth wrong with this statement:

[Week Target]/(EndDate - StartDate+1)  as Daily_Target,

What you are trying to achieve, Day level Targets?

swuehl
MVP
MVP

Your target values will be duplicated by the LEFT JOIN of your sales facts when there are more than 1 sales per date.

Have a look at

Fact Table with Mixed Granularity

and the referenced tech note.

Not applicable
Author

HI Balraj

i have multiple rows for every single branch between the start Date and End Date

so i need to map the sale date between the start Date and End Date

Anonymous
Not applicable
Author

okay, then  follow Swuehi suggestion

swuehl
MVP
MVP

You can also start with a concatenated sales and target table, like shown in attached sample.

You will notice that you split your weekly targets into 5 parts, but you will have sales on dates that are not part of your start-end intervals.

(which you lost in your previous sample).

Not applicable
Author

Thank you swuehl

it worked

silambarasan
Creator II
Creator II

How to achieve the result that appear in third table from first two tables?help me..

Table 1

Capture.PNG

Table 2

unnamed.png

Result

d.PNG

Sales will be done only based on the above pricing table, where 'Start' and 'End' field defines Quantity Range,

Example,

Product A will be sold in the price of Rupees 100 per quantity between 1-3 (qty)

The 4th qty will be sold in the price of rupees 110.