topic Re: Re: Issues formatting formula in Set analysis expression in QlikView

Issues formatting formula in Set analysis expression

Wed, 21 Aug 2013 12:04:45 GMT

Hello. I searched for a solution to my issue but was unable to find an existing thread.

The following formula works properly to display sale1 in this expression.

if (

NETWORKID = 9415.1,

sum( {$<AMP_DP_NETWORKID -= {9003.1}>} SALES1),

sum(SALES1)

)

My problem is that I would actually like to replace 'SALES1' with a more complex formula listed here:

sum(if (InDay (statistics.date, Today(), -2), SALES1)) +

sum(if (InDay (statistics.date, Today(), -2), SALES2))

I've tried to simply swap this more complex formula in places of 'SALES1' but it breaks my expression and results in blanks fields.

if (

NETWORKID = 9415.1,

sum( {$<AMP_DP_NETWORKID -= {9003.1}>} [

sum(if (InDay (statistics.date, Today(), -2), SALES1)) +

sum(if (InDay (statistics.date, Today(), -2), SALES2))]),

sum(SALES1)

)

Your help would be greatly appreciated.

Re: Issues formatting formula in Set analysis expression

lironbaram — Wed, 21 Aug 2013 12:17:34 GMT

hi try something in the line of:

if (

NETWORKID = 9415.1,

sum( {$<AMP_DP_NETWORKID -= {9003.1}>}

if (InDay (statistics.date, Today(), -2), SALES1)) +

sum({$<AMP_DP_NETWORKID -= {9003.1}>}if (InDay (statistics.date, Today(), -2), SALES2)),

sum( if (InDay (statistics.date, Today(), -2), SALES1)) +

sum(if (InDay (statistics.date, Today(), -2), SALES2)),

)

Re: Issues formatting formula in Set analysis expression

jonathandienst — Wed, 21 Aug 2013 12:51:16 GMT

I think this should do the trick:

If(NETWORKID = 9415.1,

sum({$<AMP_DP_NETWORKID -= {9003.1}>} if (InDay (statistics.date, Today(), -2), SALES1)) +

sum({$<AMP_DP_NETWORKID -= {9003.1}>} if (InDay (statistics.date, Today(), -2), SALES2)),

sum(SALES1)

)

Hope that helps

Jonathan

Re: Issues formatting formula in Set analysis expression

Thu, 29 Aug 2013 04:01:04 GMT

Thanks for the quick response and sorry the delay in mine - was traveling. This works, thank you.

It seems that I have to include NETWORKID as a dimension in my table in order for this expression to work though. Is there any way around that?

I'd love to calculate the sales based on this expression but roll them up by a different dimension in a summary table.

Thanks again.

Re: Re: Issues formatting formula in Set analysis expression

jonathandienst — Fri, 30 Aug 2013 07:56:44 GMT

I assumed that NETWORKID was something you had selected. If you want to include this on a table/chart with other dimensions, you may be able to use:

Sum(Aggr(

If(NETWORKID = 9415.1,

sum({$<AMP_DP_NETWORKID -= {9003.1}>} if (InDay (statistics.date, Today(), -2), SALES1)) +

sum({$<AMP_DP_NETWORKID -= {9003.1}>} if (InDay (statistics.date, Today(), -2), SALES2)),

sum(SALES1)

, Dim1, Dim2, Dim3))

Where Dim1 - Dim3 are the dimensions on your chart (change to the correct number and names of the chart dimensions). Whether this works or not depends though on the structure of your model and where these fields are located.

Jonathan

Edit: fixed parentheses

Re: Issues formatting formula in Set analysis expression

CELAMBARASAN — Fri, 30 Aug 2013 08:05:05 GMT

This might helps

Sum({$<NETWORKID ={9415.1},AMP_DP_NETWORKID -= {9003.1}, statistics.date={"=InDay(statistics.date, Today(), -2)"}>} SALES1) +

Sum({$<NETWORKID ={9415.1},AMP_DP_NETWORKID -= {9003.1}, statistics.date={"=InDay(statistics.date, Today(), -2)"}>} SALES2) +

Sum({$<NETWORKID -={9415.1}>} SALES1)

Re: Re: Issues formatting formula in Set analysis expression

Fri, 30 Aug 2013 11:58:23 GMT

Thanks again for your help. I do have NETWORKID selected as a dimension in another chart on another tab so your original solution definitely worked for that. Call that object 1.

This is more of a part II to my original question. Here I want to basically pull the total line from object 1 into a summary tab. I first tried to copy object 1 into the summary tab and then remove NETWORKID and add COUNTRY in the dimension list. This is making the 'if' statement = false (I presume because the argument references a NETWORKID which is now no longer in the object).

Anyway, I tried your solution below but unfortunately it didn't work. As you mentioned it must have something to do with my data model.

Any other suggestions?

Re: Re: Issues formatting formula in Set analysis expression

Fri, 30 Aug 2013 12:18:47 GMT

Thanks for your help. Using this results in "Error in set modifier ad hoc element list".