topic Re: sales diffrence calc for month and previous month in QlikView

sales diffrence calc for month and previous month

srinivasa1 — Mon, 01 Apr 2013 17:31:55 GMT

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

Anonymous — Mon, 01 Apr 2013 17:47:54 GMT

sum({$<Year={$(=max(Year))},Month={$(=month(addmonths(InvoiceDate,0)))}>}Sales)

sum({$<Year={$(=max(Year))},Month={$(=month(addmonths(InvoiceDate,-1)))}>}Sales)

Re: sales diffrence calc for month and previous month

srinivasa1 — Mon, 01 Apr 2013 20:03:16 GMT

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

Mon, 01 Apr 2013 20:14:14 GMT

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

Anonymous — Mon, 01 Apr 2013 20:25:03 GMT

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

srinivasa1 — Mon, 01 Apr 2013 20:37:12 GMT

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

srinivasa1 — Mon, 01 Apr 2013 20:39:58 GMT

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

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

Re: sales diffrence calc for month and previous month

srinivasa1 — Mon, 01 Apr 2013 20:55:24 GMT

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.

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

Anonymous — Mon, 01 Apr 2013 20:56:06 GMT

see attached

Re: sales diffrence calc for month and previous month

srinivasa1 — Mon, 01 Apr 2013 21:01:46 GMT

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

Anonymous — Mon, 01 Apr 2013 21:03:01 GMT

sum({$<Year={$(=max(Year))},Month={$(=month(addmonths(max(InvoiceDate),0)))}>}Sales)

sum({$<Year={$(=max(Year))},Month={$(=month(addmonths(max(InvoiceDate),-1)))}>}Sales)

substitute "InvoiceDate" for equivalent date value

Re: sales diffrence calc for month and previous month

srinivasa1 — Mon, 01 Apr 2013 23:05:02 GMT

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

thanks in advance

Re: sales diffrence calc for month and previous month

kaushiknsolanki — Tue, 02 Apr 2013 06:41:25 GMT

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

Anonymous — Tue, 02 Apr 2013 12:58:01 GMT

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

srinivasa1 — Tue, 02 Apr 2013 13:35:24 GMT

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

srinivasa1 — Tue, 02 Apr 2013 13:35:31 GMT

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

Anonymous — Tue, 02 Apr 2013 14:02:01 GMT

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

Re: sales diffrence calc for month and previous month

srinivasa1 — Tue, 02 Apr 2013 14:49:13 GMT

Hi ,

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

thanks in advance

Re: sales diffrence calc for month and previous month

Anonymous — Tue, 02 Apr 2013 16:37:34 GMT

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

srinivasa1 — Tue, 02 Apr 2013 16:55:35 GMT

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

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

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

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

Thanks in advance sir