topic Re: Calculate sum based on start date in QlikView

Calculate sum based on start date

Thu, 29 Jan 2015 16:52:15 GMT

I have two tables - sales and location.

Location: Has Location Code and start Date

Sales : Has Location Code and Sales for last year

I loaded sales as cross table and set up a master calendar.

I have managed to calculate the sum of sales for the last 4 months. But for this example I have hardcoded 4. I actually need to go back as far the start month for each location. For example, Location 11 needs to have the sales calculated from period 201406. How do I achieve this with the expression I have currently used? =sum({$<[PeriodID] = {">$(=Max([PeriodID])-4)"}>}Sales)

I have attached the file with this post. I also need to consider new sales data will be added for 2015.

Re: Calculate sum based on start date

Fri, 30 Jan 2015 04:40:51 GMT

Any help guys ? I have been stuck with this problem for a while now

Re: Calculate sum based on start date

arulsettu — Fri, 30 Jan 2015 05:04:59 GMT

try like this

=sum({$<[PeriodID] = {">$(=monthstart([PeriodID])-4)"}>}Sales)

Re: Calculate sum based on start date

sujeetsingh — Fri, 30 Jan 2015 05:05:22 GMT

I am not able to grasp what you need ?

Please explain according to your given sample objects that what functionality you need.

Re: Calculate sum based on start date

Fri, 30 Jan 2015 05:09:25 GMT

In the expression , I have hardcoded the number 4. As in calculate sales for the last four months,

However, this number needs to be generated based on the month of start date of the location. In the example shown, Location code 11 has a start date of 6/1/2008. The month is june. So I need to calculate the sales from june to current ( last seven months)

Please let me know if you have additional questions

Re: Calculate sum based on start date

sujeetsingh — Fri, 30 Jan 2015 06:14:57 GMT

just convert periodID as Date first. Then you have to calculate the difference of the periodid month and the Startdate month.Now you can use these autogenerated difference in months in your expression.

Or just join both tables and Calculate a flag as

=if(PeriodID>=StartDate,1,0) as CalculateFlag

Now use expression as Sum({<CalculateFlag={'1'}>}Sales)

Re: Calculate sum based on start date

Fri, 30 Jan 2015 16:13:19 GMT

Can you please provide the working example? I have attached the file in my original post.

Re: Calculate sum based on start date

Fri, 30 Jan 2015 17:26:01 GMT

Update:

I got it to work using the expression = sum({$<Month={'>=$(=num(month([Start Date])))'}>}Sales)

However this will not work when I show all locations in a table

But when I select a particular start date, it works

I gather I probably need to use aggregation by start date somewhere ? I am getting close but still not there yet. Attaching the updated file. Any help would be greatly appreciated!

Thanks,

Praveen

Re: Calculate sum based on start date

maxgro — Fri, 30 Jan 2015 19:34:52 GMT

sum(if(makedate(left(Period,4),Right(Period,2))>[Start Date], Sales))

Re: Calculate sum based on start date

Fri, 30 Jan 2015 19:54:56 GMT

Hi Massimo

Your expression is not working as I expected. I have attached the sample application for your reference.

I did get the expression to work but as i mentioned a little earlier, i need to select a specific start date to have it displayed.

Re: Calculate sum based on start date

maxgro — Fri, 30 Jan 2015 20:05:03 GMT

and what's wrong with my expression?

sum(if(makedate(left(Period,4),Right(Period,2))>[Start Date], Sales))

sum(if(makedate(left(Period,4),Right(Period,2))>=[Start Date], Sales))

can you give an example of an incorrect

location, period, sales, total sales

for my exp?

Re: Calculate sum based on start date

Fri, 30 Jan 2015 20:14:36 GMT

The expression you provided is not working for all locations. For example in the below example , it needs to calculate from period 201408 onwards only.

Re: Calculate sum based on start date

maxgro — Fri, 30 Jan 2015 21:03:35 GMT

last try

sum(if(makedate(left(Period,4),Right(Period,2))>=

if(month([Start Date])>month(today()),

makedate(year(today())-1,month([Start Date])),

makedate(year(today()),month([Start Date])) ),

Sales))

Re: Calculate sum based on start date

sujeetsingh — Fri, 30 Jan 2015 21:34:22 GMT

I can not reload the file since i do not have the sources.

Well let me give you a sample in couple of minutes.

Re: Calculate sum based on start date

sujeetsingh — Fri, 30 Jan 2015 21:44:43 GMT

Here is the sample.

Note i do not implemented the Master Calender table.

But it will resolve your issue in a best way.

Re: Calculate sum based on start date

Fri, 30 Jan 2015 22:02:40 GMT

Thank you Sujeet. This is helpful.

Re: Calculate sum based on start date

Fri, 30 Jan 2015 22:04:16 GMT

This is it.Thank you so much Massimo Grossi 🙂