topic Re: set analysis Possible combination counts in Water Cooler

set analysis Possible combination counts

shruthibk — Mon, 17 Dec 2018 08:10:50 GMT

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

Re: set analysis Possible combination counts

tresB — Mon, 17 Dec 2018 08:22:14 GMT

What is the expression you are using for text object?

Re: set analysis Possible combination counts

shruthibk — Mon, 17 Dec 2018 10:22:44 GMT

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

this is exp I am using in text object

Re: set analysis Possible combination counts

tresB — Mon, 17 Dec 2018 10:40:24 GMT

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?

Re: set analysis Possible combination counts

shruthibk — Mon, 17 Dec 2018 10:48:09 GMT

I have attached the qvf

Re: set analysis Possible combination counts

sunny_talwar — Mon, 17 Dec 2018 12:31:07 GMT

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

Re: set analysis Possible combination counts

shruthibk — Tue, 18 Dec 2018 05:57:07 GMT

expected output

submitdate,count of product id

2018-10-01, 4

2018-09-01, 3

Re: set analysis Possible combination counts

sunny_talwar — Tue, 18 Dec 2018 12:03:49 GMT

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

Re: set analysis Possible combination counts

shruthibk — Thu, 20 Dec 2018 09:49:02 GMT

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

Re: set analysis Possible combination counts

sunny_talwar — Thu, 20 Dec 2018 12:19:49 GMT

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?

Re: set analysis Possible combination counts

shruthibk — Fri, 21 Dec 2018 07:04:00 GMT

ya it will be 5 for 01-10-2018, 4 for 01-09-2018 and 1 for 01-08-2018

Re: set analysis Possible combination counts

sunny_talwar — Fri, 21 Dec 2018 12:45:57 GMT

Try this

Sum(Aggr(RangeSum(Above(Count(DISTINCT Key), 0, RowNo())), Key, (submissiondate, (NUMERIC))))