GroupBy Question

[acepeda]

Hi, i'm creating a GroupBy collection OnStart counting ocurrences at competitor_nm hierarchy level using:

ClearCollect(colPricesbyProduct2,ForAll(GroupBy(colPricebyProductF,"region_nm","country_cd","competitormaterial_cd","wave_date","division","sku_cluster_nm","competitor_nm","DATA"),{Region_NM: region_nm,Country_CD: country_cd, Competitor_Material_CD: competitormaterial_cd, WaveDate: wave_date,Division_CD: division, SKU_Cluster_NM: sku_cluster_nm,Brand_NM: competitor_nm,DATA: DATA,CountPrices: CountRows(Filter(DATA,'Price (USD)' <> Blank()))}));

This grouped collection feeds a nested horizontal gallery where records are sorted by number of occurrences (attached image as reference).  As shown, i'm using several slicers to filter records, by nested hierarchies, but specifically the date slicer linked to "wave_date" column doesn't work as i need.  Records in the gallery are splitted by wave date despite they belong to the same Brand / Product grouping.  When i omit "wave_date" from OnStart grouping collection the result it's ok but i do require to filter the gallery by Wave Date too.

I've tried using Addcolumns after grouping but i'm getting syntax errors when i call DATA.Wave_Date as Gallery filter.

Can someone give me a hand on how to incorporate this column into my collection outside of grouping and call it from gallery filtering?

Thanks in advance!.

This is my current Gallery filter Items:

SortByColumns(

Filter(

colPricesbyProduct2,

IsBlank(ddRegion.Selected.Result) || IsEmpty(ddRegion.Selected.Result) || Region_NM = ddRegion.Selected.Result,

IsBlank(ddCountry.Selected.Result) || IsEmpty(ddCountry.Selected.Result) || Country_CD = ddCountry.Selected.Result,

IsBlank(ddDivision.Selected.Result) || IsEmpty(ddDivision.Selected.Result) || Division_CD in ddDivision.Selected.Result,

IsBlank(ddSKU_Cluster.Selected.Result) || IsEmpty(ddSKU_Cluster.Selected.Result) || SKU_Cluster_NM in ddSKU_Cluster.Selected.Result,

IsBlank(ddBrand.Selected.Result) || IsEmpty(ddBrand.Selected.Result) || Brand_NM in ddBrand.Selected.Result,

WaveDate >= FromDate.SelectedDate && WaveDate <= ToDate.SelectedDate,

CountPrices > 0

),

"CountPrices",

Descending

)

[RandyHayes]

@acepeda 

You essentially appear to be grouping your data by all the columns in your datasource...although it is just a collection. What kind of column is wave_date in your datasource?

[acepeda]

Hi Randy, thanks for your answer.

wave_date is a Date column and represent specif date where records are bucked, (by waves)

[RandyHayes]

@acepeda 

So, the next question goes toward the first part of my last reply...why are you grouping on all of the columns?  Is there more data to all of that?  Are those the primary grouping?

You mentioned you are getting errors, what are the errors you are seeing?

[acepeda]

Hi,

i'm grouping at Product level which is the minimum granularity level of the table, and i'm counting ocurrences at this level by CountPrices which is counting Price ocurrences using DATA.PriceUSD column.  There are some other columns i'm not considering in the grouping but are redundant with the ones that are grouped.

If i add wave_date column into the grouping and use it for filtering my Gallery, data is spliting by that criteria generating multiple product-date groups (despite the product is the same).

I tried to add the wave_date column out of the grouping using Addcolumns but i'm confuse on where to add it in such a way that i'm able to use it as filter out of the grouped data.  

This is how i'm adding wave_date out of the grouping:

ClearCollect(
colPricesbyProduct2,
AddColumns(
ForAll(
GroupBy(
colPricebyProductF,
"region_nm",
"country_cd",
"competitormaterial_cd",
"division",
"sku_cluster_nm",
"competitor_nm",
"DATA"
),
{
Region_NM: region_nm,
Country_CD: country_cd,
Competitor_Material_CD: competitormaterial_cd,
Division_CD: division,
SKU_Cluster_NM: sku_cluster_nm,
Brand_NM: competitor_nm,
DATA: DATA,
CountPrices: CountRows(
Filter(
DATA,
'Price (USD)' <> Blank()
)
)
}
),"WaveDate",DATA.Wave_Date
));

And this is how i'm trying to filter the gallery using that column:

Despite WaveDate column is recognized by intellisense, it is rejected in the formula.

In summary, my objective is to be able to filter by WaveDate elements inside each of my groups that are constituted by several prices records of the same product.

Please let me know if the problem is better understood now. If not, I can generate a schematic so that it is better understood.

Thanks in advance for your help.

[RandyHayes · Accepted solution]

@acepeda 

Yes, you will get errors on that as you are adding a column with DATA.WaveDate.  Data is a table of records.  When you put a .WaveDate behind it, all you are doing is returning a table with only the WaveDate column...but it is STILL a table!  So you can't use it in a Filter like that.

You made one mention that perked up my ears (or eyes in this case) - "data is splitting by that criteria generating multiple product-date groups"

This is most likely due to the Time part of the Date.

Change your formula to the following:

ClearCollect(colPricesbyProduct2,
    ForAll(
        GroupBy(colPricebyProductF, "region_nm", "country_cd", "competitormaterial_cd", "wave_date", "division", "sku_cluster_nm", "competitor_nm", "DATA"),
        {Region_NM: region_nm,
         Country_CD: country_cd, 
         Competitor_Material_CD: competitormaterial_cd, 
         WaveDate: DateValue(Text(wave_date)),
         Division_CD: division, 
         SKU_Cluster_NM: sku_cluster_nm,
         Brand_NM: competitor_nm,
         DATA: DATA,
         CountPrices: CountRows(Filter(DATA,'Price (USD)' <> Blank()))
        }
    )
);

And your Filter formula should work fine then.

[acepeda]

It works!.. excellent.

Thanks a lot for your help!!