Previous year sales calculation

[ssriramin19]

ActuallyI have a table with twenty four months of data . There are sales quantity defined for each stock number for all the twenty four months .

My requirement is to calculate the previous year sales value .The previous sales value should be shown in the chart and straight table.

For example, assume for september 2013 sales is 100 and for september 2014 sales is 500 .In chart what I would be havingin the dimensions is only 2013 year.The dimensions is from jan 2014 to sep  2014 . So for corresponding year september 2014 I need to show two measures . That is both actual (value of 500) and previous year actual value (value of 100) .

Is it possible to achieve the requirement in the front end of the chart  with the change in the expression . How to determine the previous year actual value in the front end itself ?

Can anyone please guide me ?

[ashfaq_haseeb]

Hi,

use below formulaes

Current Year Sales

=Sum({<Year = {'$(=Max(Year))'}>} Sales)

Previous Year Sales

=Sum({<Year = {'$(=Max(Year)-1)'}>} Sales)

Regards

ASHFAQ

[ssriramin19]

I have all the three month date and year in my year field and it does not contain only year data .But I created a variable (Year(date)) .But this variable Year  cannot be used in the expression. How to proceed further in framing the above expression?

[ashfaq_haseeb]

Hi,

May be you need to split year month fields.

What type of data you have can you post some sample application.

Regards

ASHFAQ

[ssriramin19]

I have split year and month and stored in two variables . After this how should I proceed with above formula to be incorporated for finding previous year sales value .Since the size  of the application is large I am unable to post it here .

[ashfaq_haseeb]

Split at back-end and store in a separate field instead.

Regards

ASHFAQ

[ssriramin19]

even after storing in separate field I am unable to get the previous year sales value.

Below is the entire query for the same ,

actual:
select distinct plng_perd_strt_Dt,perd_date,sum(dmd_plng_qty) as sales
from
dgdmrv07.VGDMR_FPM_ADQ

where mmm_id_nbr='61500026069' and dmd_mgmt_ownr_code='P079'
group by 1,2
order by 1 ;

actual_data:

load

sales,
year(PLNG_PERD_STRT_DT) as year,
Month(PLNG_PERD_STRT_DT) as month

resident actual;

Please do the needful .

Kindly help me

[ssriramin19]

Please anyone help me at the earliest . I need to complete soon this requirement.

[ashfaq_haseeb]

Hi,

Can you post few sample lines of data to look over.

Remember Set Analysis is case sensitive.

Here you define year with small letters. In formula I gave you Y is capital.

Regards

ASHFAQ

[ssriramin19]

Please find the attached file for the same.