topic Re: Problem with set analysis in sum function in QlikView

Problem with set analysis in sum function

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

Hi all,

I have a chart which displays a list of vouchers and their usage. KPIs include : issue amount, amount used and remaining amount.

The goal is to display a list of vouchers used within a period of dates. Attached is an image which represents the underlaying data and the chart.

KPI expressions:

Issue amount = sum({<[Usage date]={">=$(v_UsageDateStart) <=$(v_UsageDateEnd)"}>} Issue Amount)

Amount used = sum({<[Usage date]={">=$(v_UsageDateStart) <=$(v_UsageDateEnd)"}>} Amount Used)

Remaining Amount = Issue amount - Amount used

The problem is with the "Amount Used" because it takes into account only usages occuring within the date selection. The client wants that it includes all usages per voucher occuring until the $(v_UsageDateEnd).

I tried using TOTAL in the expression but it's not working (same result as previously)

Amount used = sum(TOTAL<[Voucher Code]> {<[Usage date]={">=$(v_UsageDateStart) <=$(v_UsageDateEnd)"}>} Amount Used)

The following is not working either because it modifies the perimeter of the search :

Amount used = sum({<[Usage date]={"<=$(v_UsageDateEnd)"}>} Amount Used)

I was thinking about Aggr() function but not sure how to implement it.

Thank you in advance for the advice!

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 15:21:39 GMT

So, for Amount Used, you want to see 4.7 + 35 for Voucher Code 111?

Re: Problem with set analysis in sum function

edemerdjieva — Wed, 19 Dec 2018 15:25:44 GMT

This is the idea but only for KPI Remaining Amount.

For voucher 111 we should have :

Used Amount (only within the search periode) = 4.7

Remaining Amount = 10.3 (50 - 35 - 4.7)

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 15:29:20 GMT

So, this should work... I think

RangeSum(
 Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Issue Amount]),
 -Sum({<[Usage date] = {"<=$(v_UsageDateEnd)"}>} [Amount Used])
)

Re: Problem with set analysis in sum function

edemerdjieva — Wed, 19 Dec 2018 15:34:15 GMT

I tried this expression but then all usages are displayed (even those outside the date range).

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 15:42:16 GMT

Can you share a screenshot of when you use this expression?

Re: Problem with set analysis in sum function

edemerdjieva — Wed, 19 Dec 2018 15:48:24 GMT

Here it is.

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 15:50:30 GMT

May be do this

If(Column(1) <> 0,
RangeSum(
 Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Issue Amount]),
 -Sum({<[Usage date] = {"<=$(v_UsageDateEnd)"}>} [Amount Used])
))

Re: Problem with set analysis in sum function

edemerdjieva — Wed, 19 Dec 2018 15:54:07 GMT

Now there is only the usage within the date range but remaining amount should be 0 (image attached).

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 15:58:50 GMT

I don't think I understand the problem with the most recent image... what is wrong here?

Re: Problem with set analysis in sum function

edemerdjieva — Wed, 19 Dec 2018 16:03:26 GMT

There are two usages:

21/04/2018: usage of 11.80€

03/09/2018: usage of 8.20€

The issue amount is = 20€.

There is only one usage for the period 01/09/2018 - 30/09/2018 : 8.20€

But the total amount used is 20€ (8.20+11.80). So that's why the Remaining value should be = 0.

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 16:08:38 GMT

How about this...

If(Column(1) <> 0,
RangeSum(
 Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Issue Amount]),
 -Sum(TOTAL <[Voucher Code]> {<[Usage date] = {"<=$(v_UsageDateEnd)"}>} [Amount Used])
))

Re: Problem with set analysis in sum function

edemerdjieva — Wed, 19 Dec 2018 16:15:42 GMT

Yes, this one worked 🙂 !

Can you explain what is the role of

Column(1)

Thanks a lot for your help !

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 16:17:14 GMT

I believe we were seeing two rows... -11.80 and 11.80 and you didn't want to see the one with -11.80. So the If statement will remove the one where your issued amount = 0.

Re: Problem with set analysis in sum function

edemerdjieva — Wed, 19 Dec 2018 16:19:04 GMT

Ok but why two rows ? It's due to the RangeSum function I think but don't know why.

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 16:25:13 GMT

Because you are using Usage Date as one of the dimensions and for this particular Voucher Code... you have two dimensions for this set analysis

[Usage date] = {"<=$(v_UsageDateEnd)"}

So, it shows both :)... I am not sure why do you think it should not show 2 rows? If you remove RangeSum and use this... you don't see two rows (without if and without rangesum)?

Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Issue Amount])
-Sum(TOTAL <[Voucher Code]> {<[Usage date] = {"<=$(v_UsageDateEnd)"}>} [Amount Used])

Re: Problem with set analysis in sum function

edemerdjieva — Wed, 19 Dec 2018 16:35:17 GMT

You're right, it shows two rows.

Initially, I had the following expression which is showing only one row 🙂

Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Issue Amount])
-Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Amount Used])

Thanks a lot !

Re: Problem with set analysis in sum function

sunny_talwar — Wed, 19 Dec 2018 16:44:40 GMT

Yes... and you know why? Because Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Amount Used]) was only showing a single Usage date... but this Sum({<[Usage date] = {"<=$(v_UsageDateEnd)"}>} [Amount Used]) shows 2. So, in order to get rid of the row with 0 issue amount... use an if statement. You don't need RangeSum...