cancel
Showing results for 
Search instead for 
Did you mean: 

Adding a column with no aggregation

amelia
10 - ETL
10 - ETL

Hi Sisense community 🙂 I have imported an excel sheet into my elasticube data model and would like to display the columns in the excel sheet in a pivot table in a widget. I do not want to aggregate the values from the Excel sheet using SUM() or COUNT(), but I get an error if I do not do an aggregation. 

Attached are screenshots - the column from the excel sheet is how I would like the data to appear on Sisense pivot table, but the column I am seeing on Sisense has different values as it uses the SUM() function.

7 REPLIES 7

harikm007
13 - Data Warehouse
13 - Data Warehouse

Hi @amelia , 

Have you tried adding it under 'Rows' panel? 

harikm007_0-1653589305157.png

-Hari

 

Hi Hari, I tried that and it wouldn't work, since the rows that are missing in the Excel sheet will not show up (The data from the excel sheet only starts April 2022, but the rest of the data is from 2020 onwards)  if I do this - is there any workaround for this that you would recommend? 

Screen Shot 2022-05-26 at 2.40.36 PM.png

harikm007
13 - Data Warehouse
13 - Data Warehouse

Can you try Max() or Min() aggregators?

If its not giving expected result, can you show sample data from excel and the expected result? 

-Hari

Sure, here is a screenshot of the sample data in Excel: 

 

Screen Shot 2022-05-26 at 3.46.39 PM.png

I would like the column called 'Target' to show up as is, in the pivot table widget. I plan to add a column called 'Target' in the pivot table with these exact values, so I do not want to aggregate them. 

harikm007
13 - Data Warehouse
13 - Data Warehouse

Just to make sure if I understood the problem correctly:

There are 2 connected tables. One contains data back from 2020 and the another table, which is created from excel, contains data from 2022.

You need to create a pivot that contains all dates (from 2020) and display corresponding 'Target' (So if date is 2020 'Target' would be blank and if date is 2022 it would show the target value)

Screenshot above shows 'Weeks in calendar_date' instead of 'Days in calendar_date'. Is that expected?

-Hari

Hi Hakim, thanks for your help 🙂 I managed to solve it, appreciate it!

What was your solution? Could you share it with everyone?