topic Re: Previous year sales calculation in QlikView

Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 10:08:17 GMT

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 ?

Re: Previous year sales calculation

ashfaq_haseeb — Wed, 15 Oct 2014 10:13:32 GMT

Hi,

use below formulaes

Current Year Sales

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

Previous Year Sales

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

Regards

ASHFAQ

Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 10:31:01 GMT

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?

Re: Previous year sales calculation

ashfaq_haseeb — Wed, 15 Oct 2014 10:39:58 GMT

Hi,

May be you need to split year month fields.

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

Regards

ASHFAQ

Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 10:43:01 GMT

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 .

Re: Previous year sales calculation

ashfaq_haseeb — Wed, 15 Oct 2014 10:45:54 GMT

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

Regards

ASHFAQ

Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 11:11:57 GMT

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

Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 11:20:47 GMT

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

Re: Previous year sales calculation

ashfaq_haseeb — Wed, 15 Oct 2014 11:26:39 GMT

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

Re: Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 11:39:43 GMT

Please find the attached file for the same.

Re: Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 11:43:01 GMT

Please help me on this , on how to determine the previous year actual value.

Please do the needful

Re: Previous year sales calculation

ashfaq_haseeb — Wed, 15 Oct 2014 11:43:23 GMT

No date field available

I think you upload wrong version.

Regards

ASHFAQ

Re: Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 11:46:11 GMT

sorry for the incorrect file being attached. The attached file is the correct one..

Re: Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 11:52:38 GMT

Please help me to complete the request. since this is of very priority it needs to be completed

Re: Re: Re: Previous year sales calculation

ashfaq_haseeb — Wed, 15 Oct 2014 11:53:09 GMT

Hi,

Check this

Regards

ASHFAQ

Re: Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 12:01:58 GMT

No it should not be like that. I will explain again .

There are two values of sales for the month of sep 2014 and sep 2013 . For sep 2014 sales is 1650 and for sep 2013 it is 3000 .

So in the chart I will be showing all the dates .

It has to be like

date sales previous sales

sep 2014 1650 3000

This is the requirement . This is how it needs to be displayed in the straight table.

Please help me .

Sorry if I have made any mistake in explaining the requirement.

Re: Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 12:09:39 GMT

Please help me at the earliest . Please guide me

Thanks

Sriram

Re: Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 12:18:28 GMT

Please any one help on the requirement.

Since this is of priority , it needs to be completed.

Re: Re: Re: Previous year sales calculation

ashfaq_haseeb — Wed, 15 Oct 2014 12:19:11 GMT

Hi,

Check this

Regards

ASHFAQ

Re: Re: Re: Re: Previous year sales calculation

ssriramin19 — Wed, 15 Oct 2014 12:27:32 GMT

But instead of two rows in the dimension , I need only one rows . Only september 2014 should be present. For september 2014 it needs to display both sales (for sep2014) and previous year sales (for sep 2013)

Date	Sales	Previous year sales value
September 2014	1650	3000

The chart should be like the one above.