topic Re: Straight Table calculation in QlikView

Straight Table calculation

Fri, 06 Nov 2015 10:45:41 GMT

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

Re: Straight Table calculation

swuehl — Fri, 06 Nov 2015 10:57:58 GMT

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.

Re: Straight Table calculation

Fri, 06 Nov 2015 11:09:11 GMT

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

Re: Straight Table calculation

Anonymous — Fri, 06 Nov 2015 11:15:37 GMT

Sth wrong with this statement:

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

What you are trying to achieve, Day level Targets?

Re: Straight Table calculation

swuehl — Fri, 06 Nov 2015 11:18:47 GMT

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.

Re: Straight Table calculation

Fri, 06 Nov 2015 11:19:53 GMT

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

Re: Straight Table calculation

Anonymous — Fri, 06 Nov 2015 11:21:43 GMT

okay, then follow Swuehi suggestion

Re: Straight Table calculation

swuehl — Fri, 06 Nov 2015 11:27:57 GMT

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

Re: Straight Table calculation

Fri, 06 Nov 2015 11:38:30 GMT

Thank you swuehl

it worked

Re: Straight Table calculation

silambarasan — Wed, 23 Dec 2015 11:27:25 GMT

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

Table 1

Table 2

Result

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.