Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
=Sum(Aggr(Sum(TOTAL <[Ticket ID]> Sales), [Action ID], [Ticket ID]))
May be this?
Sum({<[Ticket ID] = P({<[Action ID] = {A}, [Ticket ID] = P({<[Action ID] = {A}>} [Ticket ID])>}[Ticket ID])>} Sales)
Hi Sophie,
How about:
=Sum(Sales)
Please find attached an example.
Hope this helps you.
With kind regards,
Ronald
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
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 ?
Can you please tell us Why Ronald's solution is not working for you?
I mean where it is giving you wrong!
My bad, stalwar1 may have some inputs?
It doesn't work because in my qvd, I have multiples lines per ticket because of item id.
May be this
=Sum(Aggr(Sum(TOTAL <[Ticket ID]> Sales), [Action ID], [Ticket ID]))
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.