Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table like this:
ID Code Code Type Dollar Amount
1 111 Red $100
1 112 Red $150
1 113 Green $120
1 114 Blue $50
2 117 Green $170
2 119 Blue $3
2 119 Yellow $30
3 201 Red $1
I want to create a flag of some sort to be able to count totals of the code type either by ID or overall.
For example:
ID 1 I would want a total of $250 for red, $120 for green and $50 for blue.
Overall, I would want a total of $251 for red, $290 for green, $53 for blue, and $30 for yellow.
Finally, I want to be able to exclude flags. So for example, if I wanted to include IDs that had red and exclude IDs that had a yellow associated with it, I'd exclude all of the values for ID 2, and only total the sums for ID 1 and 3, giving me a total of $251 for red, $120 for green and $50 for blue . Any suggestions would be appreciated.
I might not not have full understood your issue, but don't you already have a flag in the form of Code Type? In the screenshot below, when I select Code Type = Red, I get this
Using the expression
=Sum({<[Code Type], ID = P(ID)>}[Dollar Amount])
But this one expression will work for all different selection of Code Type. For example, if you select Green, You will get this
While selection Yellow give this
If you still want a script based solution, I can help you with that using a LinkTable concept.
Why do you need flags for this? You data is already aligned for the things that you mentioned. For first part of your question you can just create a visual with ID and [Code Type] as dimension and and Sum([Dollar Amount]) as the expression.
For the second part you can use set analysis to achieve this. I am not sure why you need flags here? Can you provide more details as to what the end goal is?
Hi Sorry about that! I had been so focused on it, I completely mis-explained things. The totals I explained are what it is doing NOW with the "Code Type" field. I want to total the sum of the amount for anything that included a code type. So if i wanted to get a total for "red" code types, I'd sum all of the amounts for ID 1 and ID 3. So $421 for Red. After posting this, I figured out that if i create a separate table for each Code type like shown below, I am able to achieve this, but am wondering if there is a better way.
TempRed:
Load distinct
ID as ID2
1 as RedFlag
resident Table where "Code Type"='Red';
Load distinct
ID as ID2
1 as RedFlag
resident Table where not "Code Type"='Red' and not exists(ID2, ID);
Red:
Load distinct
ID2 as ID
RedFlag
resident TempRed;
Drop Table Red.
Repeat for each color.
Any better ideas?
But are you looking to do this in the script? Can you may be do this on the front end of the app? It might be much easier to do using set analysis.
I might not not have full understood your issue, but don't you already have a flag in the form of Code Type? In the screenshot below, when I select Code Type = Red, I get this
Using the expression
=Sum({<[Code Type], ID = P(ID)>}[Dollar Amount])
But this one expression will work for all different selection of Code Type. For example, if you select Green, You will get this
While selection Yellow give this
If you still want a script based solution, I can help you with that using a LinkTable concept.
Script based solution
Table: LOAD * INLINE [ ID, Code, Code Type, Dollar Amount 1, 111, Red, $100 1, 112, Red, $150 1, 113, Green, $120 1, 114, Blue, $50 2, 117, Green, $170 2, 119, Blue, $3 2, 119, Yellow, $30 3, 201, Red, $1 ]; For i = 1 to FieldValueCount('Code Type') LET vCodeType = FieldValue('Code Type', $(i)); LinkTable: LOAD DISTINCT ID, '$(vCodeType)'& 'Flag' as Flag Resident Table Where [Code Type] = '$(vCodeType)'; NEXT
and now you can create a filter object for Flag and then select RedFlag within Flag and Sum([Dollar Amount]) will show you 421
Sorry I was reading your initial replies via email and didn't see the screenshots. This works, thank you!