Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

skamoun84
New Contributor

KPI calculation question

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 IDTicket IDSalesDiscount sales
A1-5.99
A2-6.99
-111.98
-217.98
-335.99

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

ActionSum of sales for total tickets linked to an action id
A29.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

1 Solution

Accepted Solutions

Re: KPI calculation question

May be this

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


Capture.PNG

View solution in original post

10 Replies

Re: KPI calculation question

May be this?


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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: KPI calculation question

Hi Sophie,

How about:

=Sum(Sales)

Naamloos.png

Please find attached an example.

Hope this helps you.

With kind regards,

Ronald

skamoun84
New Contributor

Re: KPI calculation question

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 IDTicket IDItem IDSalesDiscount sales
A1RC1-5.99
A2RC2-6.99
-1RC31.98
-1RC410.00
-2RC57.98
-2RC610.00
-3RC735.99

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

Any other ideas ?

Regards,

sophie

skamoun84
New Contributor

Re: KPI calculation question

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 ?

aarkay29
Valued Contributor

Re: KPI calculation question

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

My bad, stalwar1‌ may have some inputs?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
skamoun84
New Contributor

Re: KPI calculation question

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

Re: KPI calculation question

May be this

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


Capture.PNG

View solution in original post

Partner
Partner

Re: KPI calculation question

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.