10 Replies Latest reply: Nov 3, 2016 2:37 PM by Igor Gorpinich

# Set analysis comparison

Hello, good people from qlikview community.

I have some questions about set analysis. There are some calculations that seem right but it doesn't show the right answer.

Firstly:

in my first straight table I want to compare the current month with the previous month and show the percentage of the difference between the two months:

But I get some strange percentages there:

my expression is:

Sum({1<OrderYearMonth = {\$(selectedYearMonth)}>}LineTotal)/1000-Sum({1<OrderYearMonth = {\$(prevYearMonth)}>}LineTotal)/1000

I just do my first expression minus the second and then show it as the percentage.

Secondly:

In my second pivot table I want to compare current month and the whole year. I've succeded to get information in the table about the current month but it doesn't go well with the whole year.My script is that:

Sum({1<OrderYearMonth = {\$(YearSelected)}>}LineTotal)/1000

Subsequently, I do not have the right percentage here either.

Just in case, I attach my file.

• ###### Re: Set analysis comparison

For first issue, try this:

Sum({1<OrderYearMonth = {\$(selectedYearMonth)}>}LineTotal)/Sum({1<OrderYearMonth = {\$(prevYearMonth)}>}LineTotal)-1

• ###### Re: Set analysis comparison

Thanks a lot, it works.

• ###### Re: Set analysis comparison

Check for second requirement

• ###### Re: Set analysis comparison

I think it works partly.

It doesn't show correct data for SelectedYear variable.When you click on another year it still shows the same data.

What could I do with it so it shows me the right data?

Does it have anything to do with my variable SelectedYear?

• ###### Re: Set analysis comparison

I am not really sure right now, but if you can share the year selection where it isn't working I can try to dig deeper to check it out.

Best,

Sunny

• ###### Re: Set analysis comparison

YearSelected= =year(MaxSelectedOrderDate)

it looks like that

just in case another one:

MaxSelectedOrderDate=max({\$}OrderDate)

I do not understand why it always stays the same. It always shows the data from the year 2014

Maybe it ha to do with max({\$}OrderDate)? and it shows the max year but then I do not know the way to find the selected year?

• ###### Re: Set analysis comparison

I think I was using the incorrect field in the set analysis filter. I was using OrderYearMonth instead of OrderYear. Changing that seems to have helped. Check it out.

Sum({1<OrderYear = {\$(YearSelected)}, OrderYearMonth>}LineTotal)/1000

• ###### Re: Set analysis comparison

These are the variables I use to solve something like this:

maxSelectedOrderDate= max(OrderDate)

YearSelected= year(maxSelectedOrderDate)

MonthSelected= Month (maxSelectedOrderDate)

selectedYearMonth= YearSelected*100 + MonthSelected

prevYearMonth= YearSelected*100 + if(MonthSelected>1, MonthSelected-1, -88)

selectedMonthYear= Date (maxSelectedOrderDate, 'MMM-YYYY')

prevMonthYear= Date (Monthstart (maxSelectedOrderDate -1, 'MMM-YYYY')

prevYearSelected= YearSelected - 1

expression selectedMonthYear =num(sum({1<OrderYearMonth = {\$(selectedYearMonth)}>} LineTotal)/1000, '\$###0;(\$###0)')

expression prevMonthYear =num(sum({1<OrderYearMonth = {\$(prevYearMonth)}>} LineTotal)/1000, '\$###0;(\$###0)')

expression difference =num((sum({1<OrderYearMonth = {\$(selectedYearMonth)}>} LineTotal)-sum({1<OrderYearMonth = {\$(prevYearMonth)}>} LineTotal))/sum({1<OrderYearMonth = {\$(prevYearMonth)}>} LineTotal), '#%;(#%)')

Hope this helped you out!

• ###### Re: Set analysis comparison

Merci, Evi

• ###### Re: Set analysis comparison

I also want to paint the difference table red and bold if they reach 1/12 percent difference.

=if(Sum({1<OrderYearMonth = {\$(selectedYearMonth)}>}LineTotal)/Sum({1<OrderYearMonth = {\$(YearSelected)}, OrderYearMonth>}LineTotal) - 1) < 8.3, RGB(255, 128, 128))

It says me that the last part of my script is not right. What could it be there?