topic Re: Set analysis comparison in QlikView

Set analysis comparison

Mon, 17 Oct 2016 19:05:25 GMT

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.

Thank you in advance for your help and +1 for your karma.

Just in case, I attach my file.

Re: Set analysis comparison

sunny_talwar — Mon, 17 Oct 2016 19:11:41 GMT

For first issue, try this:

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

Re: Set analysis comparison

Mon, 17 Oct 2016 19:16:11 GMT

Thanks a lot, it works.

Re: Set analysis comparison

sunny_talwar — Mon, 17 Oct 2016 19:20:50 GMT

Check for second requirement

Re: Set analysis comparison

Tue, 18 Oct 2016 14:39:07 GMT

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?

Thanks in advance

Re: Set analysis comparison

Tue, 18 Oct 2016 14:49:04 GMT

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?

thanks in advance

Re: Set analysis comparison

sunny_talwar — Tue, 18 Oct 2016 16:48:34 GMT

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

Tue, 18 Oct 2016 17:29:59 GMT

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

sunny_talwar — Tue, 18 Oct 2016 19:21:53 GMT

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

Thu, 03 Nov 2016 11:24:09 GMT

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

Thu, 03 Nov 2016 18:37:37 GMT

Merci, Evi