topic Re: to compare fields in QlikView

to compare fields

Fri, 28 Mar 2014 11:51:22 GMT

I want to display the agent field for transactions for this year and previous year.

How can I solve this?

Re: to compare fields

Fri, 28 Mar 2014 11:56:43 GMT

What is agent field? Please provide some sample data.

Vinay

Re: to compare fields

manojkulkarni — Fri, 28 Mar 2014 11:57:50 GMT

Pls could elaborate more on what is the agent field & where do you want to display ?

Re: to compare fields

Fri, 28 Mar 2014 12:05:37 GMT

Hi,

Current year <Year = {"$(=max(Year))"}>

Previous year <Year = {"$(=max(Year)-1)"}>

Re: to compare fields

Fri, 28 Mar 2014 12:08:36 GMT

the agent field contains the agent of that particular transaction and i want to display of this year and of the last year to check whether it changed or not

Re: to compare fields

Fri, 28 Mar 2014 12:21:38 GMT

the agent field contains the agent of that particular transaction and i want to display of this year and of the last year to check whether it changed or not

Re: to compare fields

Fri, 28 Mar 2014 12:21:49 GMT

the agent field contains the agent of that particular transaction and i want to display of this year and of the last year to check whether it changed or not

Re: to compare fields

Fri, 28 Mar 2014 12:38:42 GMT

Hi,

Please see below might be helpful...

Load * inline
[yr,sale
2010,200
2011,100
2013,50
2014,40
]

;

Current year expression

sum({<yr= {'$(=year(Today()))'}>}sale)

Previous month expression

sum({<yr= {'$(=year(Today())-1)'}>}sale)

Regards

Vinay

Re: to compare fields

DavidFoster1 — Fri, 28 Mar 2014 12:40:35 GMT

I think we need to understand your data model. From your description above it reads like a transaction last for many years and can have many agents over that time. I suspect that is not the case. Is there a customer/account in the model as well that links the transactions together?

Re: to compare fields

Fri, 28 Mar 2014 13:26:29 GMT

i have a table that have transactions throughout the years and each transaction is associated with a particular agent .. so i want to make a table so that when i choose a particular year a table that contains the client details and the agent of this year and the agent of the previous year for each transaction is displayed. So that i can identify those records that changed agent

Re: to compare fields

DavidFoster1 — Fri, 28 Mar 2014 13:38:34 GMT

That makes a bit more sense.

So your table would have the columns

CLIENT

AGENT

AGENT LAST YEAR

And the only connection in your data from CLIENT to AGENT is through transactions.

I think you are going to have to make the assumption that the AGENT field is the most common agent from transactions in the current year selected and the AGENT LAST YEAR field is the most common agent from transactions in the previous year.

something like (I have not tested these):

AGENT = firstsortedvalue(AGENT, COUNT(AGENT))

AGENT LAST YEAR = firstsortedvalue({<YEAR={"YEAR-1"}>} AGENT, COUNT(AGENT))

Re: to compare fields

mangalsk — Fri, 28 Mar 2014 13:59:09 GMT

Hello,

PFA

Hope it works

Re: to compare fields

Fri, 28 Mar 2014 15:25:07 GMT

but te year is according to my selection so i cannot use yearnow

Re: to compare fields

mangalsk — Sat, 29 Mar 2014 05:14:37 GMT

Hello,

try this for agent acc to year selction:

Create variable vCur=max(year1)

In list box write expression==if(year1=$(vCur),agent)

By default it will show current year agent but acc to year selection it changes

Re: to compare fields

mangalsk — Sat, 29 Mar 2014 05:17:21 GMT

Hello,

PFA

I have updated qvw acc to your requirement.

Please check it.

Re: to compare fields

Fri, 04 Apr 2014 06:42:41 GMT

still no result as when i choose a particular agent , the agent of that year is filled but that of the previous year is left null if it is different

Re: to compare fields

mangalsk — Fri, 04 Apr 2014 10:31:25 GMT

Hello,

You can use alternate state.

Create two alternate states in Document properties and use in list box