Gain and Loss

[#1]

I need to make an expression to measure ‘Gain and Loss’.

If you look at this data example, I like eg. to show:

LOSS:

I like to show how many pieces of any product has been sold by all those who bought a certain product in a certain year but not the following year.

Let’s look at an example:

Using my filter in the dashboard I could chose Year = 2015 and Product = A. Then I like to show/measure how many pieces of any product has been sold BUT ONLY for Ids who bought Product A in 2014 AND NOT in 2015.

Looking into data I can see that ID 1 bought Product A in 2014 but not in 2015 (product C and B should appear then appear in my bar chart).

ID 2 has bought Product in 2014 and 2015 as well and thereby shouldn’t appear.

ID 3 bought Product A in 2014 but not in 2015 (product C should appear then appear in my bar chart).

The result will be a bar graph with the following number:

Product C = 17

Product B = 6

[reddy-s]

Hi Kenneth,

This should help you get the answer:

I have used the sample data you have given and built a bar chart:

Make use of this as your measure expression : Sum({$< Product = e({< Product = {$(=getfieldselections(Product))} >}Product), Year = e({< Year = {$(=getfieldselections(Year))} >}Year) >}Quantity)

- Sangram Reddy

[#3]

Hi Sangram,

Thanks for your reply. It looks good choosing product A. But somehow chossing another product I don't get the result I like. Ive added antoehr line to the data file to check product B:

I can see that ID 3 is the only one who has bought product B i 2014 but not in 2015. Therefore the bar chart should only show Product C = 7.

The trick should be first to pick out the IDs  who has bough the product in 2014 and not in 2015 and the then bar chart should display the product bought in 2015.

[reddy-s]

Hi Keneeth,

Check the attached QVF.

So as you needed, when you select 2014 and product B, only 2015 product C is shown up with a Quantity of 7.

- Sangram