Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shruthibk
Creator
Creator

set analysis Possible combination counts

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

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

image.png

View solution in original post

11 Replies
tresesco
MVP
MVP

What is the expression you are using for text object?
shruthibk
Creator
Creator
Author

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

 

this is exp I am using in text object

tresesco
MVP
MVP

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
Creator
Creator
Author

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
Creator
Creator
Author

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

image.png

shruthibk
Creator
Creator
Author

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?