topic Re: KPI calculation question in QlikView

KPI calculation question

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

Hi all,

Sorry to bother you but I am struggling with this issue and I can't do what I want.

I have a fact table that looks like this :

Action ID	Ticket ID	Sales	Discount sales
A	1	-	5.99
A	2	-	6.99
-	1	11.98
-	2	17.98
-	3	35.99

I want to create a chart table with the following result :

Action	Sum of sales for total tickets linked to an action id
A	29.96 (=11.98+17.98)

Can someone help me write the formula of the expression to get this result please ?

Thanks in advance,

Regards,

Sophie

Re: KPI calculation question

Anil_Babu_Samineni — Thu, 26 Oct 2017 13:22:39 GMT

May be this?

Sum({<[Ticket ID] = P({<[Action ID] = {A}, [Ticket ID] = P({<[Action ID] = {A}>} [Ticket ID])>}[Ticket ID])>} Sales)

Re: KPI calculation question

RonaldDoes — Thu, 26 Oct 2017 13:23:10 GMT

Hi Sophie,

How about:

=Sum(Sales)

Please find attached an example.

Hope this helps you.

With kind regards,

Ronald

Re: KPI calculation question

Anonymous — Thu, 26 Oct 2017 13:50:52 GMT

Thanks for your answers Ronald, but I think I have explained my initial table incorrectly because I forgot about the filed Item ID.

Here is the complete fact table

Action ID	Ticket ID	Item ID	Sales	Discount sales
A	1	RC1	-	5.99
A	2	RC2	-	6.99
-	1	RC3	1.98
-	1	RC4	10.00
-	2	RC5	7.98
-	2	RC6	10.00
-	3	RC7	35.99

I tried your solutions and it doesn't work...

Any other ideas ?

Regards,

sophie

Re: KPI calculation question

Anonymous — Thu, 26 Oct 2017 13:52:06 GMT

Thanks but since I am in a simple table chart, how can I assign [Action ID] = {A} ? What if I have an action id = B ?

Re: KPI calculation question

aarkay29 — Thu, 26 Oct 2017 13:58:06 GMT

Can you please tell us Why Ronald's solution is not working for you?

I mean where it is giving you wrong!

Re: KPI calculation question

Anil_Babu_Samineni — Thu, 26 Oct 2017 14:02:12 GMT

My bad, stalwar1‌ may have some inputs?

Re: KPI calculation question

Anonymous — Thu, 26 Oct 2017 14:09:09 GMT

It doesn't work because in my qvd, I have multiples lines per ticket because of item id.

Re: KPI calculation question

sunny_talwar — Thu, 26 Oct 2017 14:11:22 GMT

May be this

=Sum(Aggr(Sum(TOTAL <[Ticket ID]> Sales), [Action ID], [Ticket ID]))

Re: KPI calculation question

RonaldDoes — Thu, 26 Oct 2017 14:13:35 GMT

Hi Sophie,

Ok, that really doesn't change much with regards to the solution, but I've changed the demo accordingly.

SourceTable:

LOAD * INLINE [

ActionID, TicketID, ItemID, Sales, Discount sales

A, 1, RC1, , 5.99

A, 2, RC2, , 6.99

, 1, RC3, 1.98,

, 1, RC4, 10.00,

, 2, RC5, 7.98,

, 2, RC6, 10.00,

, 3, RC7, 35.99,

];

Actions:

LOAD

ActionID,

TicketID,

[Discount sales]

Resident SourceTable;

Tickets:

LOAD

TicketID,

Sales

Resident SourceTable;

DROP Table SourceTable;

Hope this helps you.

Re: KPI calculation question

Anonymous — Thu, 26 Oct 2017 14:24:21 GMT

Thanks a lot