set analysis Possible combination counts

[shruthibk]

Hi All,

I have sample data as below 

ID,Location,market,submitdate

01,CA,America,'2018-10-01',
01,TX,America,'2018-10-01',
02,CA,America,'2018-10-01'
01,PA,America,'2018-09-01',
02,TX,America,'2018-09-01',
01,TX,America,'2018-09-01'
01,CA,America,'2018-08-01'

expected output

submitdate,count of product id

2018-10-01,        5

2018-09-01,         4

2018-08-01,         1

Logic is for  check the possible  (ID &Location) with previous months

I am able to achieve this in text object but I am not getting how to achieve in the chart with dimension as date .

Thanks,

Shruthi B K

[tresesco]

What is the expression you are using for text object?

[shruthibk]

=count({<Key=p({<submissiondatenew={'$(=max(submissiondatenew))'}>}Key)>}Key)

this is exp I am using in text object

[tresesco]

So, Key and submissiondatenew are two new fields that are there in your data model. Could you share your model with sample data to work on?

[shruthibk]

I have attached the qvf 

[sunny_talwar]

What is the expected output based on the sample data provided? It seems that you don't have 2018-08-01 anymore?

[shruthibk]

expected output

submitdate,count of product id

2018-10-01,        4

2018-09-01,         3

[sunny_talwar]

I am still trying to figure out how you are getting to your output... this is how your raw data look like... can you explain us the logic behind the output needed

[shruthibk]

Logic is for the current month count of Key and also the same keys if it is present in the previous months 

for 01-10-2018 (01CA+01TX+02CA and same ids if present in previous months is  01TX) =4

for 01-09-2018 (count of ids 01PA+01TX+02TX ) and no previous month so the overall count is 3

[sunny_talwar]

So, just the previous month or if you have 01-08-2018 with 01TX, you would see 5 for 01-10-2018, 4 for 01-09-2018 and 1 for 01-08-2018? or would it be 4 for 01-10-2018, 4 for 01-09-2018 and 1 for 01-08-2018?