Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Month | Sales |
---|---|
January | 10 |
February | 20 |
March | 30 |
April | 40 |
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:
Month | Sales |
---|---|
January | 10-30 = -20 |
February | 20-30 = -10 |
March | 30-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 !
May be this?
Sum(Sales) - Sum({state2<Month = {'$(=Date(Max(Month),'MMMM'))'}>}TOTAL Sales) // I assume, state2 is alternate state
May be this?
Sum(Sales) - Sum({state2<Month = {'$(=Date(Max(Month),'MMMM'))'}>}TOTAL Sales) // I assume, state2 is alternate state
Hi Anne,
try
Sum(sales)-Sum({<Month=state2::Month>} sales)
Regards,
Antonio
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)
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)
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.