Calculate the count based on previous month products

Question

I would like to know how can I get the exact new product count for data like below.

Month | product | Amount

1st Jan 2020 | TV | 100

3rd Jan 2020 | sofa | 200

4th Feb 2020 | TV | 300

7th Feb 2020 | furniture| 500

in the above case, I need output like below.

month|product count|Amount

Jan 2020 | 2 | 300

Feb 2020 | 1 | 800

if you look at the February product count, it is only one because TV already came as part of the previous ( January ) month.

any one suggest how can we achieve this in obiee.

Gianni Ceresa · Answer

user12863665 wrote:Thanks Gianni for the update. Actually I am looking for correct product count based on the month From the original data

What is "correct"? What is "wrong"?

Keep in mind you are the only one seeing that model, that data, that screen. We don't have it.

By default a count must always be correct, or why would someone even model it like that if it's wrong?

You just aren't giving any info to have a proper answer, and also not a word about the version you are using. You must do some extra effort and be as explicit as possible if you expect the community to gives you hints.

user12863665 · Answer

Thanks Gianni for the update. Actually I am looking for correct product count based on the month From the original data

Gianni Ceresa · Answer

Hi,

Are you sure your example is correct?

Because in your example all the rows of January = 100 + 200 = 300 => what you expect, all the rows of February = 300 + 500 = 800 => what you expect.

Which means that all you need is to take the month level of your date (you must have that in your time dimension or you can make it up by formula worst case), get rid of your product column and simply sum the "Amount" and you are done.