topic Re: if else.. issue in QlikView

if else.. issue

karthikeyan1504 — Tue, 25 Aug 2015 09:05:47 GMT

Hi,

I have a requirement as briefed below,

Please refer the table below,

account id, description, amount

1, Salary, 100

2, Bonus, 20

from above table I need to build a chart to show the salary & Salary + Bonus trendline. If condition doesn't helps..

Salary = 100

Salary+Bonus = 100 + 20 = 120

Could anyone please help me on this?

Thanks & Regards,

Karthikeyan.

Re: if else.. issue

kaushiknsolanki — Tue, 25 Aug 2015 09:10:08 GMT

Hi,

You can create an intermediate table like shown below.

account id, description, amount

1, Salary, 100

2, Bonus, 20

description,Display

Salary,Salary+Bonus

Bonus,Salary+Bonus

Link this table in your data model and then use the Display field in dimension to get required output.

Regards,

Kaushik Solanki

Re: if else.. issue

Peter_Cammaert — Tue, 25 Aug 2015 09:11:11 GMT

Usually, a trendline is time-based. Do you have historical information on Salaries and Bonuses?

Peter

Re: if else.. issue

karthikeyan1504 — Tue, 25 Aug 2015 09:18:31 GMT

Yes I do have...

Let me rephrase the table,

Year, account id, description, amount

2009,1, Salary, 100

2010,2, Bonus, 20

2010,1, Salary, 150

2011,2, Bonus, 20

2012,1, Salary, 200

2013,2, Bonus, 20

2014,1, Salary, 250

2015,2, Bonus, 20

Re: if else.. issue

karthikeyan1504 — Tue, 25 Aug 2015 09:20:58 GMT

Hi Kaushik,

Thanks for your reply.

In your case it will be as two measures. but i want them into dimensions.

Warm Regards,

Karthikeyan.

Re: if else.. issue

kaushiknsolanki — Tue, 25 Aug 2015 09:24:45 GMT

Hi,

Could you tell me what output is expected from the table which you have shown.

Regards,

Kaushik Solanki

Re: if else.. issue

karthikeyan1504 — Tue, 25 Aug 2015 09:32:35 GMT

Re: if else.. issue

kaushiknsolanki — Tue, 25 Aug 2015 09:37:47 GMT

Hi,

I guess what you have done is right.

You will need two expression one for sales and other for Sales + Bonus.

If not then please help us to understand with example and sample data.

Regards,

Kaushik Solanki

Re: if else.. issue

karthikeyan1504 — Tue, 25 Aug 2015 09:47:14 GMT

Year & Salary Expense is the dimensions

Amount is the measure

Re: if else.. issue

kaushiknsolanki — Tue, 25 Aug 2015 09:52:45 GMT

Hi,

You can have Year as dimension.

1st Expression will be Sum(Salary)

2nd Will be Sum(Salary) + Sum(Bonus) Give label as Salary + Bonus

Show them as Line chart and you are done.

Regards,

Kaushik Solanki

Re: if else.. issue

Peter_Cammaert — Tue, 25 Aug 2015 09:58:09 GMT

I guess you want something like:

Some set analysis is helpful. However, it should work with an IF() as well. Set Analysis performs better

Peter

Re: if else.. issue

Peter_Cammaert — Tue, 25 Aug 2015 10:00:27 GMT

BTW I was assuming that a real trend line could easily jump over missing data points. For example, 2011 has no salary data but the Trend line will interpolate between 2010 and 2012.

Re: if else.. issue

jagan — Tue, 25 Aug 2015 10:09:05 GMT

Hi,

Try like this

Dimesion: account id

Salary Expression: Sum({<description={'Salary'}>} amount)

Salary + Bonue Expression: Salary Expression: Sum({<description={'Salary', 'Bonus'}>} amount)