Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
i have a table with Date, 15 Min Interval time, product no, ID & Name in my qvw.
Sample Input in the excel sheet attached
i have to provide a grouped result for a day at 15 min intervals like below. i have to find how many singles, doubles & triples in a day
But i have to exclude 'N/A' in the 'Product Details' field for my grouped result.
Each Product No will have multiple Product details associated with it. for example, in the above table, for 09/10/2019 at 12:00:00, there are 3 products with the same product no. so my result for 12:00:00 should be
09/10/2019 12:00:00 1 (Triple)
similarly, at 00:30:00, 3 products with product no SA711, but 2 of them shows N/A in product details. so i have to exclude that 2. my result at 00:30:00 should be
09/10/2019 00:30:00 1(Single)
so My entire output for the above table should be like:
Output:
Date | 15 Min Intervals | Single | Double | Triple |
09/10/2019 | 00:00:00 | 1 | 1 | 0 |
09/10/2019 | 00:15:00 | 1 | 1 | 0 |
09/10/2019 | 00:30:00 | 1 | 0 | 0 |
09/10/2019 | 01:15:00 | 0 | 1 | 0 |
09/10/2019 | 01:30:00 | 1 | 1 | 0 |
09/10/2019 | 01:45:00 | 0 | 1 | 1 |
09/10/2019 | 06:00:00 | 0 | 1 | 1 |
09/10/2019 | 06:15:00 | 0 | 1 | 1 |
09/10/2019 | 12:00:00 | 0 | 0 | 1 |
I have attached the input and output data in excel.
Can you please help me with the expression to get this result?
Try the below expressions:
Single:
Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=1,[Product No]))
Double:
Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=2,[Product No]))
Triple:
Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=3,[Product No]))
There you go:
Create your logic at script level:
Aggregate your table by [Product No], then create link key using [Date] and [15 Min Intervals].
Use the new count column from the aggregated table above at front end with set analysis to filter count by 1,2,3.
Thanks and regards,
Arthur Fong
Hi Arthur,
Thank you for your response.
but i have to bring the solution through expression.
i cannot do the changes in script as my fields are segregated in different tables.
can you please help me with an expression to get the result
Try the below expressions:
Single:
Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=1,[Product No]))
Double:
Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=2,[Product No]))
Triple:
Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=3,[Product No]))
thank you so much for your help arthur
Thank you so much for your help tresesco