topic Re: from IF to set analysis in QlikView

from IF to set analysis

Tue, 22 Sep 2015 09:02:08 GMT

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'.

Re: from if expression into a set analysis expression

tresB — Tue, 22 Sep 2015 09:14:56 GMT

It looks fine. Where are you using this expression? In a chart ? If so, what are the dimensions? Could you post a sample of your qvw?

Re: from IF to set analysis

arulsettu — Tue, 22 Sep 2015 09:16:52 GMT

maybe this

sum(

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

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

>} Orders.Aantal_Artikelen)

or post a sample app

Re: from IF to set analysis

Tue, 22 Sep 2015 09:31:14 GMT

I'm using this expression in a pivot table. What do you mean with posting a sample?

Re: from IF to set analysis

Tue, 22 Sep 2015 09:32:49 GMT

Unfortunately this does not work. The expression still returns a different value.

Re: from IF to set analysis

tresB — Tue, 22 Sep 2015 09:43:34 GMT

Attach a small sample qvw. Click on 'use advanced editor' (top right corner) ->Attach (bottom right)

Re: from IF to set analysis

sasiparupudi1 — Tue, 22 Sep 2015 09:45:35 GMT

please try

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

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

>} [Orders.Aantal_Artikelen])

Re: from IF to set analysis

Tue, 22 Sep 2015 09:54:33 GMT

this expression does not work, it results in --> -

Re: from IF to set analysis

Tue, 22 Sep 2015 11:26:30 GMT

Hi,

what columns do you use in the pivot table? (dimensions and metrics)

Your set analysis is correct, so there must be something else that is causing the problem..

seems strange that your "New situation" works, but not your latest complete set

/Pierre

Re: from IF to set analysis

Anonymous — Tue, 22 Sep 2015 11:36:11 GMT

The "doesn't work" and "returns a different value" is not exactly the same... You'd better attach an example as tresesco‌ sais.

Re: from IF to set analysis

krishna20 — Tue, 22 Sep 2015 11:36:15 GMT

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)

Re: from IF to set analysis

Tue, 22 Sep 2015 11:49:52 GMT

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?

Re: from IF to set analysis

sasiparupudi1 — Tue, 22 Sep 2015 12:04:50 GMT

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])

Re: from IF to set analysis

Tue, 22 Sep 2015 12:34:56 GMT

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.

Re: from IF to set analysis

Tue, 22 Sep 2015 13:08:56 GMT

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 )

Re: from IF to set analysis

sasiparupudi1 — Tue, 22 Sep 2015 13:13:46 GMT

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

Re: from IF to set analysis

Anonymous — Tue, 22 Sep 2015 14:41:30 GMT

Hi, try.

if ( Orders.Bedrag_Exclusief <>0,sum(

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

>}Orders.Aantal_Artikelen))

Re: from IF to set analysis

Thu, 15 Oct 2015 09:20:24 GMT

Solution:

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