Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis: Nested If Statement into Set Analysis

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

1 Solution

Accepted Solutions
clisboa_noesis
Partner - Creator
Partner - Creator

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

View solution in original post

7 Replies
Not applicable
Author

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

Not applicable
Author

Thank you...This is good,

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

clisboa_noesis
Partner - Creator
Partner - Creator

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

Not applicable
Author

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.


chris_johnson
Creator III
Creator III

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

clisboa_noesis
Partner - Creator
Partner - Creator

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

Not applicable
Author

Thank you,

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