Creating aggregate calculations with more level of details in Alteryx

[sreekanthac]

Hello Community,

I am facing the following situation. My customer has created a dashboard in Tableau but it is very slow and takes more time to apply various filters. Now I am trying support him to improve its performace. After inital analysis I understood that he has performed several calculations in Tableau some of which are using IF statements. All these seems to take more time since for each filter, Tableau goes through the IF statements and then make the necessary calculations and so on. The customer is willing to get the calculations done in Alteryx if that can help the dashboard work smoother. 

I will try to explain his caculations with a dummy data attached below. All the calculations shown here are sample version.

Region	Country	City	Order Date	Sales	Qty
North	Philippines	Panay	31.05.2021	€36,77	18
North	Ireland	Malahide	24.11.2021	€38,91	97
South	Peru	Lahuaytambo	21.05.2020	€26,80	39
West	Georgia	T’ianet’i	11.07.2020	€29,23	15
North	Malaysia	Kuantan	28.05.2021	€14,33	4
East	Philippines	Ilaya	13.02.2021	€38,09	79
West	Czech Republic	Topolná	09.10.2020	€47,13	6
South	China	Hohhot	10.03.2021	€24,89	33
West	Russia	Golovchino	03.04.2021	€31,84	12
South	Guatemala	Concepción	30.12.2021	€48,42	62
East	China	Libu	14.03.2021	€18,89	1
North	Russia	Novyye Cherëmushki	26.10.2021	€20,71	64
East	Philippines	Napnapan	23.07.2020	€10,77	4
West	Afghanistan	Qal‘ah-ye Kūf	03.03.2020	€41,81	54
West	Venezuela	Upata	22.06.2020	€46,45	87
South	Pakistan	Shahkot	01.12.2021	€28,72	59
South	Morocco	Oued Laou	27.01.2020	€11,09	46
East	China	Wudui	25.11.2020	€49,76	29
North	Portugal	Lagoa de Albufeira	16.12.2021	€45,87	6
North	Canada	Altona	18.11.2021	€20,83	100

The sales happened in 2021 are classified as current period and those in 2020 as previous period. This classification is performed using a IF statement. He has done the following calculations in Tableau:

Sales current = SUM(IIF( current, Sales, 0)

Sales prev = SUM(IIF( prev, Sales, 0)

Qty current = SUM(IIF( current, Qty, 0)

Qty prev = SUM(IIF( prev, Qty, 0)

Price current = Sales current/Qty current

Price prev = Sales prev /Qty prev

These are aggregated level of calculations which is easier in Tableau. I can get it done in Alteryx using Summarise tool but then I miss the level of details. The customer wants different levels of details such as Region or Country or City to be displyed in the dashboard and all the calculations should be calculated accordingly. 

How can these calculations be done in Alteryx so the level of details are not missed when we use the output in Tableau. Dummy attached for your reference.

I am additing the pictures from Tableau which I created using the dummy data. As you can see in the picture, for each level of deails the current and previous sales are aggregate. The user can decide which levels of details he wants to show and the values are updated accordingly. 

Thanks in advance for your suggestions. 

[ArtApa]

Hi @sreekanthac - Hope I understood your challenge correctly:

[sreekanthac]

@ArtApa I have added some pictures from tableau to make my question more clear.

As shown in the picture, user can added more level of details in the views to get the values updated accodingly. I am trying to achieve this in Alteryx. Using summarize tool I get the aggregated value but then I miss this level of details. 

[danilang]

hi @sreekanthac 

You can probably improve the performance of the dashboard by pre-transposing the data.  This reduces the need to calculate the period and assign the corresponding values within Tableau.  

Another possible optimization would be to convert the Sales to a numeric field, but this might already be done automatically within Tableau.  If it's not done automatically, then move the conversion formulas into Alteryx as well.  The two price calculations will have to remain in Tableau, since you're calculating an average price for the group and this will depend on the aggregation level.

Push the results directly to a Tableau data source as opposed writing to another excel file.  

Dan

[sreekanthac]

Thanks @danilang for your response. I am not sure whether pre transoposing would work with a dataset containing around 100 columns and millions of rows (which is the case with me). The Sales field is already in numeric field. In this example its strings though. As I said, the provided dataset is only an example, as I was looking for some suggestions on whether the aggregate caculations could be done better in Alteryx. :)