topic Need Help in Set Expression in QlikView

Need Help in Set Expression

md_qlikview — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Need Help in Set Expression

karthikoffi27se — Mon, 31 Jul 2017 07:15:43 GMT

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

Re: Need Help in Set Expression

md_qlikview — Mon, 31 Jul 2017 08:13:54 GMT

Hi Karthik,

Thnx for your reply but I am not getting desired output

Re: Need Help in Set Expression

nirav_bhimani — Mon, 31 Jul 2017 09:36:46 GMT

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

Re: Need Help in Set Expression

md_qlikview — Mon, 31 Jul 2017 09:40:27 GMT

Hi Nirav,

Thnx for reply. Will check and let you know.

Re: Need Help in Set Expression

karthikoffi27se — Tue, 01 Aug 2017 04:59:39 GMT

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.