30 Replies Latest reply: Apr 2, 2013 1:31 PM by Kaushik Solanki

# sales diffrence  calc for month and previous month

Hi,

i have a field year contains 2011,2012,2013 and month filter jan,feb,mar etc

i need to calculate the difference sales between current selected year  month and previous month.(ie)

if i selected year 2011 then  feb month then the result should find difference between jan and feb.....

pls,help me out thanks in advance.

-

• ###### Re: sales diffrence  calc for month and previous month

Hi

Im looking for Sales diffrance eg if select year 2011 and month Jan and Feb.then sales diffrance will be Jan(sales)-Feb(sales)

• ###### Re: sales diffrence  calc for month and previous month

So are you saying you'll always select exactly two months and that too consecutive months?

• ###### Re: sales diffrence  calc for month and previous month

No.Any range selected month or year.just like Select CY Month sales- CP Month sales

eg

sum (Feb(sales)-Jan(sales))  as diffrance between two month for selected Year

• ###### Re: sales diffrence  calc for month and previous month

sum({\$<Year={\$(=max(Year))},Month={\$(=TextBetween(GetFieldSelections(Month),'',','))}>}Sales)

-

sum({\$<Year={\$(=max(Year))},Month={\$(=TextBetween(GetFieldSelections(Month),',',''))}>}Sales)

will do exactly as you described

• ###### Re: sales diffrence  calc for month and previous month

Sir,

It looks good but when i added my experssion part its not working.can pls upload sample app.

Thanks

• ###### Re: sales diffrence  calc for month and previous month

Thanks .my mistake missed some filed,If i select range of two month its  showing properly diffrance .but im looking it should do calculate if not selected also.

Eg

Year selected (if not selected year it should be shown for month on month diffrance for all the Year)

2011

Jan-Feb dif  | Feb-Mar dif  so on in pivot table(ie Jan sales is 200- feb sales 100)

100               200

2012

Jan-Feb dif  | Feb-Mar dif  so on in pivot table

500               200

2013

Jan-Feb dif  | Feb-Mar dif  so on in pivot table

60                       70

• ###### Re: sales diffrence  calc for month and previous month

i like the way you done it might usefull to me in future analysis.i wll upload some sample app by today evening so you understand well thanks for try.

• ###### Re: sales diffrence  calc for month and previous month

-

substitute "InvoiceDate" for equivalent date value

• ###### Re: sales diffrence  calc for month and previous month

Hi,

Thanks for your reply.i have tried but it shows only for max year by default i would like to show diffrance for all the year.

I have attached my sample app

• ###### Re: sales diffrence  calc for month and previous month

Hi,

Have a look at the application, hope this is what you want.

Regards,

Kaushik Solanki

• ###### Re: sales diffrence  calc for month and previous month

I liked Kaushik's approach. I modified it slightly but I think either illustrates the core of the idea in using the before() and after() functions.

• ###### Re: sales diffrence  calc for month and previous month

Thanks.

I like go with before option

But same time jan cols I need to hide as its coming as
zero.or otherwise month dim should be look  (jan-feb) instead of jan,feb as its now
showing

And also let me know is any other approach handle this by  writing set analysis or if condition

• ###### Re: sales diffrence  calc for month and previous month

Thanks.

I like go with before option

But same time jan cols I need to hide as its coming as
zero.or otherwise month dim should be look  (jan-feb) instead of jan,feb as its now
showing

And also let me know is any other approach handle this by  writing set analysis or if condition

• ###### Re: sales diffrence  calc for month and previous month

Sorry. But I don't understand what you are asking.

• ###### Re: sales diffrence  calc for month and previous month

Hi ,

I mean How i can handle  month on month diffrance for mutiple year selection by useing setanalysis insted of before function

• ###### Re: sales diffrence  calc for month and previous month

Let's clarify first. This is what I'm understanding so far.

This is what we have:

 YEAR 2011 MONTH JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC SALES - FEB - JAN MAR - FEB APR - MAR MAY - APR JUN - MAY JUL - JUN AUG - JUL SEP - AUG OCT - SEP NOV - OCT DEC - NOV Selections: YEAR 2011

You've said that the scenario will be such that the field selections for YEAR will be 2011 and 2012 (for example). In that situation, you want the chart to do something different to handle the multiple selections.

What exactly do you want it to do?

i.e.

possible results:

- if 2011 and 2012, then show just 2012 (12 total values in output)

- if 2011 and 2012, then show just 2011 (12 total values in output)

- if 2011 and 2012, then show both 2011 and 2012; calculate month over month for each (24 total values in output)

- if 2011 and 2012, then show cumulative difference for both years; (Mar2011+Mar2012) - (Apr2011+Apr2012) --> (12 total values in output

There are more possibilities but those were a few that quickly came to mind to offer as examples. The current set up you have will show the 3rd option with 24 values in the output. If you want something different, please describe the intended output.

• ###### Re: sales diffrence  calc for month and previous month

Sorry to make you worry ,Thanks for your effort looking result as below

- if  i select from slider 2011
and 2012 and 2013, then 2011 and 2012 and 2013; calculate month over month diffrance

1. Eg. If select
2011 as Year then my month on month difference should be as below

 Year 2011(for 2011) Month Jan2011-Feb2011 Diff Feb2011-Mar2011 Diff Mar2011-Apr2011 diff Apr-may diff May-Jun Jun-July July-Aug Aug-Sep Same way Till Dec 200 200 100 50 500 250 60 600

Eg. If select 2012 as Year then my month on
month difference should be

 Year 2012(for 2012) Month Jan2012-Feb2012 Diff Feb2012-Mar2012 Diff Mar2012-Apr2012 diff Apr-may diff May-Jun Jun-July July-Aug Aug-Sep Same way Till Dec 200 200 100 50 500 250 60 600

1. Eg. If select
20123 as Year then my month on month difference should be

 Year 2013(For 2013) Month Jan2013-Feb2013 Diff Feb2013-Mar2013 Diff Mar2013-Apr2013 diff Apr-may diff May-Jun Jun-July July-Aug Aug-Sep Same way Till Dec 200 200 100 50 500 250 60 600

1. Eg. If select
2011,2012,2013 as Year then my month on month difference should be same as
above in one pivot table first it will show month on month difference 2011 after
2012 and 2013

• ###### Re: sales diffrence  calc for month and previous month

dont worry on data Its just exmple of row data diffrance .

• ###### Re: sales diffrence  calc for month and previous month

Hi,

Have a look at the example.

Regards,

Kaushik Solanki

• ###### Re: sales diffrence  calc for month and previous month

It looks good let me check. iwll get back to you thanks

• ###### Re: sales diffrence  calc for month and previous month

Thanks sir i have checked

Same way I need calculate for all the month of year (by default
all the year (2011,2012,2013 if year selected then only for that year all the
month  )can you pls let me know I do that

• ###### Re: sales diffrence  calc for month and previous month

Dear Ram,

In My example i have create seperate expression for each selection. If you need the different by default for the years, then you will have to create expression for each and then you can use the enable condition to show the relevent exxpression for that year.

There is no other way to do this.

Regards,

Kaushik Solanki

• ###### Re: sales diffrence  calc for month and previous month

Thanks sir let me try.can you pls let me know one think how people will handle month on month diffrance for 3 to 4 years value in qlikview

• ###### Re: sales diffrence  calc for month and previous month

Hi,

In general scenario, people will always like to see the data for current year at a glance, most users are not interested in looking the data for all years at one shot.

So keeping this in mind i have given you an example where by default it will take the max year and as you select year it will change for that year.

Regards,

Kaushik Solanki

• ###### Re: sales diffrence  calc for month and previous month

Thanks for input.my requiremnets for all the year .can set analysis or if condtion also not help in this scenario?

• ###### Re: sales diffrence  calc for month and previous month

Hi,

As far as i know this is not possible with Set Analysis.

Regards,

Kaushik Solanki

• ###### Re: sales diffrence  calc for month and previous month

Check Attachment...is this what you're looking for?

• ###### Re: sales diffrence  calc for month and previous month

Thanks sir,im looking something the way Kaushik Solanki done.is this possible handle in pivot table for mont on mont diffrance.