How to select a constant view of selected customers from one month going forward?

[Anonymous]

Hi All,

I have 2 tables that join on a  masterkey.

The master key is created by : client number x month

eg: if a camp was in Jan 2016(1601)  and the client number was 1, the masterkey = 1601.

The first table:

Camp_name

M_key

Second table:

M_key

client_number

spend

month

I am trying to produce a graph that will show a client and how thier spend changed following the camp.

I want to be able to select the camp, and the graph will only show those clients that went on that camp and the spend for those clients across all months worth of data that we have.

I tried this in the graph as it is kinda what I want to see once a selection of the camp is made:

=SUM({<client_number={$},month={1}>}[Spend])

Any help will be awesome!

Thanks in advance.

[tresesco]

May be like:

=SUM({<month>}[Spend])

[Anonymous]

I have tried that, but it only shows the month that the camp took place due to Table one's Masterkey linked to the month.

If the camp took place in jan, and we select the camp, I want to see the client that was part of that camp, not only his/her spend for jan but for all months of data that I have.

I want to see the client's lifetime spend.

[Pulkit_Thukral]

Seems your primary dimension has to be month and secondary to be a drill down group having Camp_name and then client_number.

and in the expression SUM([Spend])

[tresesco]

Are you selecting month?

[Anonymous]

No, the camp is linked to a month through the masterkey.

So it automatically selects the month, the graph though, shows only the month spend when i select a camp.

I want it to show all spend for those clients that are in a camp across all months.

I am not sure if it is possible looking at the way the tables are set up?

[Anonymous]

I am not quite sure that will work.

I want to be able to select a camp, which selects all client's numbers that are part of that camp, which then shows the sum of the those clients across all months data that I have for that client.

This was the bad example that I used for the graph:

=SUM({<client_number={$},month={1}>}[Spend])

I looked into creating an expression into the listbox to only select client_numbers when a camp is selected - that way the graph will be correct, showing every month of spend for those clients in the camp... But I am not sure how to do that either?

[tresesco]

Try to create a qvw with sample data set and share here; explain your expected output against certain selections. That would help us help you better.

[Anonymous]

Thank you for your help so far.

Attached is the example.

When the selection of the camp 'aaaa' is made I want to see the graph as saved, both clients that are part of the camp 'aaaa'

ignoring the other clients sum.

If you select camp it shows only the date spend for that camp.

[tresesco]

Please upload the sample with at least two camps data.