topic Re: Set Analysis expression equivalent to If expression in QlikView

Set Analysis expression equivalent to If expression

Wed, 12 Oct 2011 05:58:34 GMT

Dear friends,

I have written an expression using If in the below manner -

Sum(IF(PrevMY=InvMonthYear, InvoiceAmount, 0))

Note: in the above expression PrevMY, InvMonthYear and InvoiceAmount are the fields in QVD files.

Can anyone help me to write Set Analysis equivalent expression of the above one?

Thnaks in advance.

Ishraque Ahmad

Re: Set Analysis expression equivalent to If expression

Sokkorn — Wed, 12 Oct 2011 06:11:21 GMT

Hi Ishraque,

Let try this SUM({$<PrevMY = {$(InvMonthYear)}>} InvoiceAmount)

Let me know if this one working.

Regards,

Sokkorn

Re: Set Analysis expression equivalent to If expression

Wed, 12 Oct 2011 06:29:23 GMT

Hi Sokkorn,

Thanks for your reply. Applying this expression gives different result than the Original one.

The result of your suggested expression is equivalent to the result of Sum(InvoiceAmount)

Thanks and Regards.

Ishraque Ahmad

Set Analysis expression equivalent to If expression

SunilChauhan — Wed, 12 Oct 2011 06:35:23 GMT

try the following

SUM({<PrevMY = {"$(InvMonthYear)"}>} InvoiceAmount)

Re: Set Analysis expression equivalent to If expression

Wed, 12 Oct 2011 06:37:02 GMT

Hi,

Please see the attached file,i hope this will help u

Thanks,

Vivek

Re: Set Analysis expression equivalent to If expression

Sokkorn — Wed, 12 Oct 2011 07:01:16 GMT

Hi Ishraque,

Try to load data like this

[tmp1]:

LOAD *,

IF(PrevMY=InvMonthYear,1,0) AS Flag;

LOAD * INLINE [

No,PrevMY,InvMonthYear,InvoiceAmount

1,2009,2009,30

2,2009,2010,40

3,2010,2010,50

4,2010,2011,60];

Then use set analysis SUM({$<Flag = {1}>} InvoiceAmount)

Regards,

Sokkorn

Re: Set Analysis expression equivalent to If expression

Thu, 13 Oct 2011 10:01:41 GMT

Dear friends,

I am not yet able to solve the issue.

I have extracted the faulty piece of code in Sample.qvw file and uploading it for your reference.

Please see the prectical problem and guide me if possible to apply the correct syntax of Set Analysis.

Thanks and Regards.

Ishraque Ahmad

Re: Set Analysis expression equivalent to If expression

Sokkorn — Thu, 13 Oct 2011 10:30:54 GMT

Hi Ishraque,

For me I think that you don't need to do a set analysis. Because in this case you have used disconnect table and it can be slow on printing data (test it with 10,000 record will see the problem).

Now I have one solution for you. Let try it

- Use only one table [InvoiceHeader]

- Create one Pivot Table with [InvMonthYear] as Dimensions and SUM([InvoiceAmount]) as Expressions.

- If you need to create template like your object (horizontal in MonthYear), you need to drag column [InvMonthYear] from vertical to horizontal.

- If you need to sort InvMonthYear then try do it in Sort tab with expression:

Match(LEFT(InvMonthYear,3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))) + (RIGHT(InvMonthYear,4)*100)

See the sample attached file.

Hope it help you.

Regards,

Sokkorn

Re: Set Analysis expression equivalent to If expression

Thu, 13 Oct 2011 11:23:57 GMT

Hi Sokkorn,

Thanks a lot for your reply that has helped me improving my knowledge.

The sample I have attached in my previous reply is not my exact requirement (as I have mentioned it is extracted columns, records and KPI).

There are few points in my application which will give you some idea to understand my requirement as -

Invoice Table has around 1,200,000 records with many other fields (I have extracted few fields with limited records).
Invoice Summary (UI) does not contain only the fields I am showing in the Sample. It has some other fields also displayed and then the 12 months Invoice Summary.
Invoice Summary (UI) in application is a Drilldown table having Order Type, Part Type, Shipping Mode, Trade Term, Freight Forwarder and Customer as drilldown attributes.
Above all these there are many other places where I would apply aggregate functions based on two fields matching criteria.
RequiredMY Calendar that I am loading would be dynamic and populated based on the Year selected from the UI. I have taken System Date - 2 Years in Sample to simplify it.

Main Issue is why SET is not comparing 1 field value with another field value whereas IF behaves as expected? I feel my syntax is wrong and hence want to arrive at the correct syntax.

Thanks and Regards.

Ishraque Ahmad