topic Re: Formula in Set Analysis in QlikView

Formula in Set Analysis

edemerdjieva — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Formula in Set Analysis

sunny_talwar — Wed, 05 Dec 2018 16:31:55 GMT

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)

Re: Formula in Set Analysis

Mark_Little — Wed, 05 Dec 2018 16:42:02 GMT

Hi,

Maybe not the best approach but,

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

should do the trick.

Mark

Re: Formula in Set Analysis

edemerdjieva — Wed, 05 Dec 2018 16:47:39 GMT

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.

Re: Formula in Set Analysis

sunny_talwar — Wed, 05 Dec 2018 16:53:56 GMT

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

Re: Formula in Set Analysis

Mark_Little — Wed, 05 Dec 2018 16:55:37 GMT

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

Re: Formula in Set Analysis

edemerdjieva — Wed, 05 Dec 2018 17:23:24 GMT

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.

Re: Formula in Set Analysis

edemerdjieva — Wed, 05 Dec 2018 17:15:46 GMT

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

Re: Formula in Set Analysis

sunny_talwar — Thu, 06 Dec 2018 01:49:16 GMT

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

Re: Formula in Set Analysis

Mark_Little — Thu, 06 Dec 2018 09:40:53 GMT

Hi again,

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

Mark

Re: Formula in Set Analysis

edemerdjieva — Thu, 06 Dec 2018 11:02:15 GMT

@sunny_talwar

It worked 😄 !

There were two small syntax errors, so here is the final expression :

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

Thanks a lot for the explanations.

Re: Formula in Set Analysis

sunny_talwar — Thu, 06 Dec 2018 12:17:52 GMT

Super!! I am glad we were able to figure it out in the end 🙂