topic Re: Set Analysis Case in QlikView

Set Analysis Case

aarohipatel — Mon, 01 Apr 2019 21:49:55 GMT

Hi All,

I have the following fields in one of my application:

1.YEAR_MONTH

2.ITD_PAID

I am trying to achieve the following case:

If YEAR_MONTH=2016 Then 0 Else Current Year ITD_PAID - Previous Year ITD_PAID.

Please advise

Thanks in advance.

Re: Set Analysis Case

danilostochi — Tue, 02 Apr 2019 02:56:38 GMT

Hi, check this out,

=if(YEAR_MONTH = '2016','0',Above(ITD_PAID))

Re: Set Analysis Case

jyothish8807 — Tue, 02 Apr 2019 05:20:28 GMT

Hi Aaro,

Try this:

=sum(if(YEAR_MONTH = '2016','0',ITD_PAID - Above(ITD_PAID)))

Re: Set Analysis Case

aarohipatel — Tue, 02 Apr 2019 13:37:06 GMT

Hi Danil,

Sorry for missing part for my requirement.

I need the sum (ITD_PAID)
If YEAR_MONTH=2016 Then 0 Else Sum(Current Year ITD_PAID)- Sum(Previous Year ITD_PAID).

Sorry once again.

Please advise.

Thank you!

Re: Set Analysis Case

jyothish8807 — Wed, 03 Apr 2019 05:58:33 GMT

Hi Aarohi,

Did the solution mentioned above by me worked ?

Re: Set Analysis Case

aarohipatel — Wed, 03 Apr 2019 13:45:38 GMT

Hi KC,

Sorry for missing part for my requirement in my initial post.

Like I mentioned to Danil,

I need the sum (ITD_PAID)

ITD_PAID is an expression (This field is populated with amounts)

If YEAR_MONTH=2016 Then 0 Else Sum(Current Year ITD_PAID)- Sum(Previous Year ITD_PAID).

Sorry once again.

Please advise.

Thank you!

Re: Set Analysis Case

danilostochi — Wed, 03 Apr 2019 16:01:34 GMT

hi,

see if it is, if not create an example with images.

Re: Set Analysis Case

jyothish8807 — Thu, 04 Apr 2019 05:50:32 GMT

Hi Aarohi,

The same can be achieved by this expression.

=if(YEAR_MONTH = '2016','0',ITD_PAID - Above(ITD_PAID))

Just make sure you aggregate the data at year level. That mean one entry for each year and sort the table by year.

Please refer the attached.

Re: Set Analysis Case

aarohipatel — Thu, 04 Apr 2019 17:36:19 GMT

HI KC,

How can I use this new field in a graph with YEAR_MONTH?

Even in the straight table, I cant use this new field calculation if I do not pull 'ITD_PAID'

Thanks!

Re: Set Analysis Case

jyothish8807 — Fri, 05 Apr 2019 06:33:02 GMT

Hi Aarohi,

Try this.

I just hide the ITD_PAID field in both straight and bar chart.

Re: Set Analysis Case

jyothish8807 — Fri, 05 Apr 2019 06:33:47 GMT

else you have to create the new field in back end.

Re: Set Analysis Case

aarohipatel — Fri, 05 Apr 2019 13:33:17 GMT

Thank you very much KC!

Re: Set Analysis Case

jyothish8807 — Mon, 08 Apr 2019 05:12:42 GMT

You are welcome!

Re: Set Analysis Case

aarohipatel — Mon, 22 Apr 2019 21:12:07 GMT

Hi KC,

The suggestions you have provided did answer my question but in some cases it is not working for my requirement.

Please see the attached qvw with additional requirement mentioned in a text box in the UI.

Please advise.

Thank you in advance.

Re: Set Analysis Case

jyothish8807 — Tue, 23 Apr 2019 06:18:22 GMT

Hi Aarohi,

Try this. Best approach to fulfill your requirement will be to create the fields in the back end.

Re: Set Analysis Case

aarohipatel — Wed, 15 May 2019 18:43:51 GMT

Hi KC,

I have followed the approach suggested by you to create the fields in back end (using Previous function)and it works fine.

But wanted to know how do we handle filters. I have many filters that need to be effective towards the net values.

For example, I have a filter 'Region' in a list box. When I make selections in the List box, it is not reflecting to the Net values.

I did try to pull region into the resident load but the Net values are getting affected.

B:
load
Year,
Year_month,
Region,
ITD_PAID,
if(Year=2016,0,ITD_PAID-Previous(ITD_PAID)) as NetPaid;
Load
Year,
Year_month,
Region,
Sum(ITD_PAID) as ITD_PAID
resident A
group by
Year,
Year_month,
Region
order by Year,Year_month,Region;

Also, wanted to check, instead of doing all this in back end, is there a way to do it in front-end with set analysis or any other approach apart from above function? Will there be any drawbacks with Set Analysis?

Please advise