topic Re: Set Analysis in QlikView

Set Analysis

Anonymous — Thu, 03 Nov 2016 15:26:07 GMT

Hi All,

I am trying to write an expression to get data for Previous Month and Jan to Previous month with the fields Num and Denum which are Integers. I have the StartDate field using which I have

written

Year(StartDate) as YEAR,

Month(StartDate)as MONTH,

'Q' & ceil(month(StartDate)/3)as Quarter,

date(StartDate,'MMM YY')as YearMonth,

Could you please help me to write an expression in set analysis to get Previous Month and Jan to Previous month values(should be shown in percentage with 3 decimal values).

( Also help me to understand writing equation using set analysis(any good resource or document) )

Thanks in advance,

Re: Set Analysis

johnw — Fri, 04 Nov 2016 00:11:58 GMT

Maybe something like this?

sum({<YearMonth={'$(=date(addmonths(YearMonth,-1),'MMM YY'))'},YEAR,MONTH,Quarter,StartDate>} Num)

But it only works if you can only select one month at a time. And in any case, I probably wouldn't handle this with set analysis. I'd probably handle it with an AsOf table.

AsOfYearMonth,AsOfType,YearMonth
May 2016,Current,May 2016
May 2016,Previous,Apr 2016
May 2016,Year to Prev,Apr 2016
May 2016,Year to Prev,Mar 2016
May 2016,Year to Prev,Feb 2016
May 2016,Year to Prev,Jan 2016

And then you could do something like this to build a chart comparing the current month to the previous month and Jan to previous month.

Dimension 1 = AsOfYearMonth
Dimension 2 = AsOfType
Expression = sum(Num)

Basically, move the complexity into the script rather than in the objects.

As far as set analysis resources, I mostly just read the help text, and then examples from the forum.

Re: Set Analysis

Anonymous — Fri, 04 Nov 2016 14:44:41 GMT

Thank you John, I will try to implement using the AsOf table, and will get back.

-PV