Announcements
cancel
Showing results for
Did you mean:
Not applicable

Compare data between states

Hi,

I am new to Qlik and I am stuck on a problem.

I have 2 fields : month and sales.

Let's say I have the following values

MonthSales
January10
February20
March30
April40

I also have two states: default and state2 with a list box for the month field for each state.

I would like a graph where the dimension is the selected months from the default state. The expression would be the difference with the selected month in state2.

For example, if I select every month in default and march in state 2, I would like:

MonthSales
January10-30 = -20
February20-30 = -10
March30-30 = 0
April

40-30 = 10

The expression I used is : sum({\$}[sales])-sum({state2}[sales])

But since month is the dimension and is also different in {\$} and {state2} it does not work.

(I do have other fields but I think I'll be able to do what I want if I can get this)

I hope this is clear; help would be appreciated !

1 Solution

Accepted Solutions

May be this?

Sum(Sales) - Sum({state2<Month = {'\$(=Date(Max(Month),'MMMM'))'}>}TOTAL Sales) // I assume, state2 is alternate state

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
6 Replies

May be this?

Sum(Sales) - Sum({state2<Month = {'\$(=Date(Max(Month),'MMMM'))'}>}TOTAL Sales) // I assume, state2 is alternate state

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Master III

Hi Anne,

try

Sum(sales)-Sum({<Month=state2::Month>} sales)

Regards,

Antonio

Not applicable
Author

Hi,

Thank you for your help ! But this is not working.

The result is strange, I think it's because Month is also the dimension.

What I have is:

For each month in default state: Sum(sales) (the second part of the expression equals 0)

For the month in alternate state : -Sum({<Month=state2::Month>} sales) (the first part equals 0)

Not applicable
Author

Awesome, that worked !

Even the simple "sum([sales])-sum({state2}TOTAL [sales])" works. I will read more about TOTAL because I'm not sure I understant what it does.

I have another bonus question :

I have another dimension which is the type of sale: type1 and type2 (I would like two curves on my grpah). But everything breaks when I add the dimension I want or when I add "aggr(...,type)" in the expression.

I created two expressions, one for each type and it works perfectly so it is totally optional.

Thank again for the answer !

Bonus for you only, Not happen in my life while PAR

Anyhow, May be this?

Sum(Aggr(Sum(Sales), Type)) - Sum(Aggr(Sum({state2<Month = {'\$(=Date(Max(Month),'MMMM'))'}>}TOTAL Sales), Type))

Or

Aggr(Sum(Sales), Type) - Aggr(Sum({state2<Month = {'\$(=Date(Max(Month),'MMMM'))'}>}TOTAL Sales), Type)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable
Author

Thank you for helping me again !

Those 2 expressions don't work, I think it has something to do with TOTAL. "AGGR" doesn't work well,with "TOTAL". I tried with "TOTAL <type>" instead of AGGR but it doesn't show the MONTH of default state anymore.

I guess I will keep the 2 expressions.

Community Browser