Set analysis comparison

[#1]

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.

[sunny_talwar · Accepted solution]

For first issue, try this:

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

[#3]

Thanks a lot, it works.

[sunny_talwar]

Check for second requirement

[#5]

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

[#6]

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

[sunny_talwar]

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

[#8]

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?

[sunny_talwar]

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

[#10]

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!