Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

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

Just in case, I attach my file.

1 Solution

Accepted Solutions
sunny_talwar

For first issue, try this:

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

Capture.PNG

View solution in original post

10 Replies
sunny_talwar

For first issue, try this:

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

Capture.PNG

Not applicable
Author

Thanks a lot, it works.

sunny_talwar

Check for second requirement

Capture.PNG

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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!