topic Re: Compartively analysis in QlikView

Compartively analysis

Thu, 12 Apr 2012 06:38:42 GMT

I want to compare sales of current year vs last year. for this i have used this expression

sum({$<Year={$(=only(Year))}>} LineSalesAmount)

sum({$<Year={$(=only(Year)-1)}>} LineSalesAmount)

and it works fine when i select a year field

But suppose when i dont select any year, i want the comparision between current year vs previous all years

Compartively analysis

Thu, 12 Apr 2012 06:51:57 GMT

Hi,

The Only gives you Null when you have multiple values.

You could use the Max() function instead.

Then, if you dont have any year selected the expression will use the highest year (I.e current year)...

sum({$<Year={$(=max(Year))}>} LineSalesAmount)

sum({$<Year={$(=max(Year)-1)}>} LineSalesAmount)

Compartively analysis

Thu, 12 Apr 2012 07:03:27 GMT

Suppose i have sales data from year 2005 to 2011. then how to see the sales of 2005 to 2010 vs sales of 2011

Compartively analysis

Thu, 12 Apr 2012 07:45:20 GMT

You just alter the second expression to something like the following...

sum({$<Year={$(=max(Year))}>} LineSalesAmount)

sum({$<Year={"<=$(=max(Year)-1)"}>} LineSalesAmount)

The first expression is the same.

The second expression says that Years shall be less or equal to Previous year.

Of course the following is the same:

sum({$<Year={"<$(=max(Year))"}>} LineSalesAmount)

Year shall be less to current year.

You can also have a intervall:

sum({$<Year={"<$(=max(Year))>$(=max(Year)-5)"}>} LineSalesAmount)

Year shall be less than current year and also more than current year-5

Compartively analysis

Thu, 12 Apr 2012 09:22:47 GMT

My next query is...

suppose i select year 2011 and month sep....

now i have to see sales of 2011 for jan to aug vs 2011 for sep

Re: Compartively analysis

Thu, 12 Apr 2012 09:39:28 GMT

You can do the same for months.

Expand your set expression to include month

sum({$<Year={$(=max(Year))},Month={"$(=max(Month))"}>} LineSalesAmount)

sum({$<Year={"$(=max(Year))"},Month={"<$(=max(Month))"}>} LineSalesAmount)

First expression current Year and current Month

Second expression:

Current Year and Months less than current Month

Month must contain the Months part of a date. I.e It must be a dual value to work correct.

If Month just only contains the text part of the name t vill do a text comparisson and thats is not what you want...

As long as Month is declared as something as Month = Month(<YourDate>); in script you propobly will be fine.

If you have the Monthsnumber in a field and want to use that insted, you could do that but you must cancel the month selection if you have some...

sum({$<Year={$(=max(Year))},Month=,MonthNumber={"$(=max(MonthNumber))"}>} LineSalesAmount)

sum({$<Year={"$(=max(Year))"},Month=,MonthNumber={"<$(=max(MonthNumber))"}>} LineSalesAmount)

Here I use MonthNumber which is associated with Month.
But I use Month= , to cancel out Months influense on the expression since you use a $ as a set identifier.

I recomend you to study the set analysis portion of the help file. It can be where useful...

Re: Compartively analysis

Thu, 12 Apr 2012 10:03:44 GMT

it is showing the same values..on selecting may 2011

Re: Compartively analysis

CELAMBARASAN — Thu, 12 Apr 2012 10:15:05 GMT

Hi,

do you have date field?

sum({<DateField={">=$(=YearStart(Max(DateField)))<=$(=MonthEnd(Max(DateField),-1))"},Year=,Month=>} LineSalesAmount) //sales of 2011 for jan to aug

sum({<DateField={">=$(=MonthStart(Max(DateField)))<=$(=MonthEnd(Max(DateField)))"},Year=,Month=>} LineSalesAmount)// 2011 for sep

Celambarasan

Re: Compartively analysis

Thu, 12 Apr 2012 10:26:11 GMT

i have order date field.....i have orderdate in place of date field .it is working