from IF to set analysis

[#1]

Hi all,

I'm currently working on a report which uses a lot of IF statements. I prefer to change these expressions using IF into expressions using set analysis. But I'm running into some issues..  Hopefully someone can help me out.

Old situation:

sum  ( {<Jaar, Maandnr = >} if( Jaar =GetFieldSelections(Jaar)

and Maandnr =GetFieldSelections(Maandnr)

and Orders.Order_Type='Verkoop'

and DIM_Budget_Item.Budget_Item_Omschrijving='schoenen'

and Orders.Bedrag_Exclusief<>0,

Orders.Aantal_Artikelen))

New Situation:

Rewriting the old expression I ended up with the following expression. This expression gave the same outcome as the 'old situation', but I still had to use an IF statement..

sum(

{$< DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},

Orders.Order_Type={'verkoop'}

>}

if ( Orders.Bedrag_Exclusief <>0,

Orders.Aantal_Artikelen))

What does not work..

Trying to leave out all the IF statements I ended up with the following expression, unfortunately this expression does nu give the same result as the two former expressions.

sum(

{$ < DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},

Orders.Order_Type={'verkoop'}, Orders.Bedrag_Exclusief -= {0}

>} Orders.Aantal_Artikelen)

So for now I'm stuck with the 'new situation'.

[krishna20]

Hi Try This,

sum(

{$ < DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},

Orders.Order_Type={'verkoop'}, Orders.Bedrag_Exclusief =- {'0'}

>} Orders.Aantal_Artikelen)

(or)

sum(

{$ < DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},

Orders.Order_Type={'verkoop'}, Orders.Bedrag_Exclusief <> {'0'}

>} Orders.Aantal_Artikelen)

[#3]

Hi Pierre,

Thanks for your reply. The dimension I used is 'Aantal Verkopen'. The column displays the sum of 'Aantal Verkopen' for the selected period. Other columns show the sum of 'Aantal Verkopen'  for Year To Date or the selected month of the previous year.

The latest complete set does not give an error, but a different value. Do you have an idea what can be causing the problem?

[sasiparupudi1]

Hi

The keyword sum was missing in the above expression. Please could you try again?

sum({$ < [DIM_Budget_Item.Budget_Item_Omschrijving]={'schoenen'},

[Orders.Order_Type]={'verkoop'}, [Orders.Bedrag_Exclusief] = {'*'}-{0}

>} [Orders.Aantal_Artikelen])

[#5]

Hi,

This expression gives the same result as the one I tried (with the complete set). Still the result differs from the situation that includes an if statement.

[#6]

Ok,

have you tried to include the requested period in each column?

eg. " Period={'$(=max(Period))'} " for YTD and " Period={'$(=max(Period)-12)'} " for previous YTD

so in the column for YTD:

sum({$< DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},Orders.Order_Type={'verkoop'}, Orders.Bedrag_Exclusief -= {0}, Period={'$(=max(Period))'}>} Orders.Aantal_Artikelen)

...other then that: "Antal Verkopen" (amount of sales) seems more like a measure. would try using the DIM_Budget_Item.Budget_Item_Omschrijving as a dimension... But then i don't really know what you try to accomplish, so this thought is potentially completely wrong (if so, sorry for that )

[sasiparupudi1]

Hi

Please try

sum({$ <Jaar ={"$(=max(Jaar))"}, Maandnr ={"$(=max(Maandnr))"},[DIM_Budget_Item.Budget_Item_Omschrijving]={'schoenen'},

[Orders.Order_Type]={'verkoop'}, [Orders.Bedrag_Exclusief] = {'*'}-{0}

>} [Orders.Aantal_Artikelen])

hth

Sasi

[Anonymous]

Hi,  try.

if ( Orders.Bedrag_Exclusief <>0,sum(

{$< DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},Orders.Order_Type={'verkoop'}

>}Orders.Aantal_Artikelen))

[#9]

Solution:

sum(
{$ < DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},
Orders.Order_Type={'verkoop'}, Orders.Bedrag_Exclusief={"<0>0"}
>} Orders.Aantal_Artikelen)