Formula in Set Analysis

[edemerdjieva]

Hello everybody,

I need to create an expression in a chart and filter the values by using a set analysis. Here is the idea:

KPI = Sum({<(Field 1 - Field 2)-={0}>} Amount) 

(exclude the values where the result of the formula is =0)

I am not sure if this is possible and what would be the syntax. 

Thanks for your help in advance.

[sunny_talwar]

Against which dimension are you checking Field1 - Field is not equal to 0? Lets Call it DimX... try this

Sum({<DimX = {"=(Field1 - Field2) <> 0"}>} Amount) 

[Mark_Little]

Hi,

Maybe not the best approach but,

IF( SUM(Field1 - Field2) = 0, SUM(Amount))

should do the trick.

Mark

[edemerdjieva]

Actually, it's not a dimension that I'm checking but the result of two facts. 

In the data model we have:

Dimension: Voucher

Fact 1: Voucher Amount (issue value)

Fact 2 (several possible): Voucher Amount Used (a voucher car be used partially, so many usages)

The chart should not include vouchers without remaining  value (=0). So that's why i try to filter the liste through set analysis:

 Here is an example with data: the last line  should not appear in the chart.

[sunny_talwar]

Does a Voucher code can only have one and only one voucher date issue? If it does, then you can try this

Sum({<[Voucher Code] = {"=Sum([Voucher Issue Amount]) - Sum([Voucher Used Amount]) <> 0"}>} [Voucher Issue Amount])

Sum({<[Voucher Code] = {"=Sum([Voucher Issue Amount]) - Sum([Voucher Used Amount]) <> 0"}>} [Voucher Used Amount])

[Mark_Little]

Hi Again,
The way I would approach this then would be in script and flag the Voucher Codes your interested in.

Something like
IF([Voucher Issue Amount] = [Voucher Used Amount], 0,1) AS [Voucher Outstanding],

Then in set analysis you could use something like
SUM({<[Voucher Outstanding]={1}>}[Voucher Issue Amount] = [Voucher Used Amount])

If the the above table is transactional will need a aggregate table to flag the vouchers

LOAD
[Voucher Code],
IF(SUM([Voucher Issue Amount]) - SUM([Voucher Used Amount]) = 0, 0,1) AS [Voucher Outstanding]
Resident Table
GROUP BY [Voucher Code] ;

Table being your data table.


Mark

[edemerdjieva]

Yes, there is only one date issue.

I tried your suggestion but I have some syntaxe issue because I have also set analysis in the  Sum (Voucher Amount)

Sum({<[Date]={">=$(v_DateIssue_1) <=$(v_DateIssue_2)"}>}[Voucher Issue Amount])

Here is the complete KPI in this case:

Sum({<[Voucher Code] = {"=Sum({set analysis as above}[Voucher Issue Amount]) - Sum({set analysis as above}[Voucher Used Amount]) <> 0"}, set analysis as above >} [Voucher Issue Amount]) 

 But there is a syntax issue because of the quotes in the second set analysis.

[edemerdjieva]

@Mark_Little

This would not work in load script because the chart is based on date selection. And at a given time a voucher can have a remaining value.

[sunny_talwar · Accepted solution]

Try like this

Sum({<[Voucher Code] = {"=Sum({<[Date] = {[>=$(v_DateIssue_1)<=$(v_DateIssue_2)]}>} [Voucher Issue Amount]) - Sum({"=Sum({<[Date] = {[>=$(v_DateIssue_1)<=$(v_DateIssue_2)]} [Voucher Used Amount]) <> 0"}, [Date] = {">=$(v_DateIssue_1)<=$(v_DateIssue_2)"}>} [Voucher Issue Amount]) 

Basically use [] instead of double quotes around the inner set analysis... you can also use escape sequence by using 2 double quotes within the inner set analysis

Sum({<[Voucher Code] = {"=Sum({<[Date] = {"">=$(v_DateIssue_1)<=$(v_DateIssue_2)""}>} [Voucher Issue Amount]) - Sum({"=Sum({<[Date] = {"">=$(v_DateIssue_1)<=$(v_DateIssue_2)""} [Voucher Used Amount]) <> 0"}, [Date] = {">=$(v_DateIssue_1)<=$(v_DateIssue_2)"}>} [Voucher Issue Amount]) 

[Mark_Little]

Hi again,

Hi could you please explain a little more of how the transactions look in the data, might be able suggest better?

Mark