topic Re: Set Analysis: Nested If Statement into Set Analysis in QlikView

Set Analysis: Nested If Statement into Set Analysis

Thu, 19 Jul 2012 07:15:57 GMT

Hi,

Can anyone help me to convert this If Statement into Set Analysis Expression

Sum(If(

Type='Cancelled' and

Ceil(Month(Date(Floor([Ordered Date])))/3)=

(If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,1,(Ceil(Month(Date(Floor([Fiscal Date])))/3)+1)))

and

Year(Date(Floor([Ordered Date])))=

(If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,(Year(Date(Floor([Fiscal Date])))+1),Year(Date(Floor([Fiscal Date])))))

([Amount]),0))

Thanks

Re: Set Analysis: Nested If Statement into Set Analysis

Thu, 19 Jul 2012 07:40:02 GMT

Sum({1} If(

Type='Cancelled' and

Ceil(Month(Date(Floor([Ordered Date])))/3)=

(If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,1,(Ceil(Month(Date(Floor([Fiscal Date])))/3)+1)))

and

Year(Date(Floor([Ordered Date])))=

(If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,(Year(Date(Floor([Fiscal Date])))+1),Year(Date(Floor([Fiscal Date])))))

([Amount]),0))

You can post your project beacuse id like saw the data model

Re: Set Analysis: Nested If Statement into Set Analysis

Thu, 19 Jul 2012 08:01:28 GMT

Thank you...This is good,

But I'm looking for more of a complete set analysis statement, specially on the IF statements.

Re: Set Analysis: Nested If Statement into Set Analysis

clisboa_noesis — Thu, 19 Jul 2012 08:17:36 GMT

Hi,

It would be easier for us to help you if you tell us what you're trying to do with all that Ceil(Month(Date(Floor([Ordered Date])))/3).

I looks like those parts can be replaced by some tweaks in you Calendar table.

Regards,

Carlos

Re: Set Analysis: Nested If Statement into Set Analysis

Thu, 19 Jul 2012 08:27:03 GMT

What i'm trying achieve by this is.... Sum of Cancelled Amount in Next Quarter.

Sum(If(

Type='Cancelled' and

Ceil(Month(Date(Floor([Ordered Date])))/3)=

(If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,1,(Ceil(Month(Date(Floor([Fiscal Date])))/3)+1)))

and

Year(Date(Floor([Ordered Date])))=

(If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,(Year(Date(Floor([Fiscal Date])))+1),Year(Date(Floor([Fiscal Date])))))

([Amount]),0))

Ceil(Month(Date(Floor([Ordered Date])))/3) is for Quarter Calculation.

(If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,1,(Ceil(Month(Date(Floor([Fiscal Date])))/3)+1)))

In this one I'm checking if the Quarter is of 4 then Make it 1, Which would be next quarter for 4, Else add it with 1. where i would get next quarter

Same way im doing for the year.

If Quarter is of 4th, then Increase year also by 1. by which i get 1 quarter in next year.

Re: Set Analysis: Nested If Statement into Set Analysis

chris_johnson — Thu, 19 Jul 2012 08:27:49 GMT

Hi,

I'd probably consider moving some of the calculations you are doing into the script which will make things easier to follow. So instead of having Ceil(Month(Date(Floor([Ordered Date])))/3) put that into your script and give it a field name. In fact, you may even want to move most of the calculation into script, all the bits inside the SUM() if you can.

This may have a small impact on the time to reload the document but will help in the long run when people come to use it as the front end will be quicker for it. As a general rule try to put as much into the script as possible.

Do this first and it may help you work out what you need to do for the set analysis. It will certainly help you be able to see what you're doing more clearly.

Once you've done this, or if you are having trouble working out how to do this, post back here and we can all have a look. Don't worry too much also, set analysis takes a little effort getting your head around but once you've got it you'll find it a big help as QlikView is really geared up for this sort of thing.

Chris

Re: Set Analysis: Nested If Statement into Set Analysis

clisboa_noesis — Thu, 19 Jul 2012 08:38:10 GMT

So, there's a simpler and more straight forward way to do that

Here's my take on this, i supose you have a calendar table, in that table you can create a Quarter field.

Something like this:

'Q' & ceil(month(Date)/3) as Quarter,

Now, to do what you need to do the most simple way is to create a field like 20121 (YearQuarter)

year(Date) & ceil(month(Date)/3) as YearQuarter,

And now to be able to cycle trough all quarters sequentially you can do:

AutoNumber(year(Date) & ceil(month(Date)/3)) as YearQuarterSeq,

This will assign a sequential number for each YearQuarter.

Now on you expression you should be able to simply do this:

sum( {< Year =, Month =, Quarter =, Type = {"Canceled"}, YearQuarterSeq = {"$(=max(YearQuarterSeq+1))"}>} Amount)

Hope this helps, or at least points you to the right direction.

Regards,

Carlos

Re: Set Analysis: Nested If Statement into Set Analysis

Mon, 23 Jul 2012 08:50:31 GMT

Thank you,

This seems more like it. I'll try this one.