I want to store Calculated column and use it for next month.i.e, for every month I want to use that calculated column that stored in previous month.How can I store a seperate column for every month.
Hi @nitishpala - I do not think it is possible in QuickSight. If you want to store the calculated field, you have to do it out of QuickSight ( possibly at ETL stage).
Hi @Max @David_Wong - please provide your expert advise on this.
Regards - Sanjeeb
1 Like
Hi @nitishpala - can you explain a little further what you want you visual to look like? If you are trying to build columns that represent this month, last month, etc, you can try the approach in this article: Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)
If your values from a given month (lets say Mar 2023) change over time and you are trying to snapshot what Mar 2023 looked like over time, you will need to do this in the ETL stage as @Sanjeeb2022 mentioned. Should take a snapshot of the table every month and append/union it every month with a new column indicating which month the snapshot is from. Then you can track changes between the months.
1 Like
My datasets is already present in spice. so, I am facing limitations with storing calculated data.For this use-case can you help in storing data(with no manual work).
Apologies @nitishpala, Im still not understanding what you are trying to achieve. Can you explain what columns you have in your underlying dataset and what you are trying to add to it?
Hi @Jesse, I want to store above calculated field(status), Thus for every month I have requirement of using previous month calculated values(status).
Is their any approach in QuickSight as my datasets is already present in QuickSight spice.
Hi @nitishpala - thank you for the additional info, that helps me understand. In this case unfortunately we will not be able to store those values directly from QuickSight/SPICE. As a general rule, QuickSight (any pretty much all BI tools) cannot create new rows of data. We can all create new columns (like calculated fields), but not new rows. In order to snapshot/store those values, we need to create additional rows. I see conceptually what you are thinking in terms of creating a new column with those ‘frozen’ values, but from a SQL/query perspective I dont think that is possible. We are going to have to do this as a data prep exercise. You will need to snapshot that table on monthly basis and add a new column indicating the snapshot date. From there we can write calculations to piece apart the status at each month and can detect changes. This is very common with Salesforce reporting, support ticket reporting, etc where the table is always going to show the most recent status and without snapshotting the table you are not able to track changes.
2 Likes