Need Help in Set Expression

[md_qlikview]

Hello all,

I need to create an adhoc report which has multiple dimensions and expressions.Out of all expression in one of the expression i needed to calculate sales of Current month  - Previous Month.

Eg. If i select branch it will show all branch by default with Current / Max month sales - Previous month sales.

Now in list of dimensions i have Year and month as well. For Year by default it should show all Years with  following conditions

Eg. I have two year then it should show

Year     Sales

2017     Max(Month)-Max(Month)-1

2016     Max(Month)-Max(Month)-1...

and For Month it should show all months data by default with following conditions -

Eg. considering my

July      July's Sales - June's Sales (for all years since no year selected)

June     June's Sales-May's Sales  ...and so on.

How can i achieve this ? Attaching a sample data to work around

Thanks In advance.

[karthikoffi27se]

Hi Milind,

You have to create a variable as

Let vLastMonthsale = Sum({<Month = Month(Today())>}Sales)

Let vThisMonthsale = Sum({<Month = AddMonths(Month(Today())-1>}Sales)

Now use this formula for your scenario.

July      July's Sales - June's Sales (for all years since no year selected)

June     June's Sales-May's Sales

You have do this for rest of the month and you can go up till end of last year.

Many Thanks

Karthik

[md_qlikview]

Hi Karthik,

Thnx for your reply but I am not getting desired output

[nirav_bhimani]

Hi Milind,

You can use below script to calculate variance at Monthyear,

Aggr_MOnth:

LOAD

    MonthYear,

    Sum(Sales)

FROM [lib://SalesData/Sales.qvd]

(qvd) Group By MonthYear

Order by MonthYear;

LEFT JOIN(Aggr_MOnth)

load

MonthYear,

Previous(Sales) as PMSales

Resident Aggr_MOnth;

FinalSalesVariance:

Load

MonthYear,

(Sales - PMSales) as SalesVariance,

PMSales,

Sales

Resident Aggr_MOnth;

Drop table Aggr_MOnth;

Note:

You can also have others data gran in the group by

Regards,

Nirav Bhimani

[md_qlikview]

Hi Nirav,

Thnx for reply. Will check and let you know.

[karthikoffi27se]

Hi Milind,

Sorry, try using this

Let vLastMonthsale = Sum({<Month = {$=(Month(Today()))}>}Sales)

Let vThisMonthsale = Sum({<Month = {$=(AddMonths(Month(Today())-1)}>}Sales)

or trying using Nirav solution but I personally prefer not to bring in the aggregation down to script due to  impact on load efficiency.