topic Re: Compare data between states in QlikView

Compare data between states

Wed, 25 Nov 2020 16:16:04 GMT

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 !

Re: Compare data between states

Anil_Babu_Samineni — Wed, 28 Jun 2017 15:33:33 GMT

May be this?

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

Re: Compare data between states

antoniotiman — Wed, 28 Jun 2017 15:49:13 GMT

Hi Anne,

try

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

Regards,

Antonio

Re: Compare data between states

Thu, 29 Jun 2017 08:08:15 GMT

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)

Re: Compare data between states

Thu, 29 Jun 2017 09:00:52 GMT

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 !

Re: Compare data between states

Anil_Babu_Samineni — Thu, 29 Jun 2017 17:28:10 GMT

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))

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

Re: Compare data between states

Fri, 30 Jun 2017 07:53:52 GMT

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.