topic Re: Set analysis in QlikView

Set analysis

NavinReddy — Mon, 16 Dec 2013 11:16:29 GMT

Hi All,

How to compare Current month Data and previous month Data

With one Expression

Could some one help me

Thanks In Advance

Niranjan

Re: Set analysis

Anonymous — Mon, 16 Dec 2013 11:22:52 GMT

Hi,

You can use below expressions to compare current month and previous month data:

EXP1> sum({$<Month={$(=month(today()))}>}Value)

EXP 2> sum({$<Month={$(=month(addmonths(today(),-1)))}>}Value)

Regards

Nitin

Re: Set analysis

salto — Mon, 16 Dec 2013 11:24:17 GMT

Hi,

two set analysis expressions, that will sum sales por this month and the previous month:

=sum({$<MonthNum={$(=max(MonthNum))}>} Sales)

=sum({$<MonthNum={$(=Max(MonthNum)-1)}>} Sales)

Hope this helps!

Re: Set analysis

NavinReddy — Mon, 16 Dec 2013 11:32:08 GMT

Hi,

Is not possible to write in one expression

Thanks,

Niranjan

Re: Set analysis

Mon, 16 Dec 2013 11:33:20 GMT

hi,

Create these variables

vLastMonth=month(AddMonths(MakeDate(year,month,'01'),-1))

vLastYear= =max(year-1)

Then For Last Month Use:

=if(vLastMonth='Dec',sum({$<month={$(=vLastMonth)},year={$(=vLastYear)}>}stu_result.sales_qty),

sum({$<month={$(=vLastMonth)}>}stu_result.sales_qty)

)

For Current Month Use

=sum(stu_result.sales_qty)

User will need to select current year and month in this case.

Alternatively, you and also generate a calender, use it for selection and create variable and through set analysis calculate current and last year sales.

Regards,

Arun Goel

Re: Set analysis

Mon, 16 Dec 2013 11:33:36 GMT

Of course if you want them under one expressions just subtract do a simple subscrtaction:

sum({$<month={$(=month(today()))}>}value)

sum({$<month={$(=month(addmonths(today(),-1)))}>}value)

Re: Set analysis

salto — Mon, 16 Dec 2013 11:35:35 GMT

You can write it in an expression and check the % variance between one month and the previous one:

=sum({$<MonthNum={$(=max(MonthNum))}>} Sales)

sum({$<MonthNum={$(=Max(MonthNum)-1)}>} Sales)

and format the expression to show it in percentage.

Hope this helps.

Re: Set analysis

Mon, 16 Dec 2013 11:45:33 GMT

If need one expression, load inline field:

Type:

LOAD * INLINE [

Type

Current

Last

];

Then use this "Type" Field as dimension.

In expression use

=if(Type='Current',

"Expression for current Month",

if(Type='Last',

"Expression for LastMonth"

,''))