Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Thomas23
Contributor II
Contributor II

Get the sum of previous month ignoring filter

Hi,

in my App I have a table where I show in one column the sum of values per month and in another the sum of values of the previous month. I have a filter for year and month. So when I select '2020' I see all values except for december 2019 (but there are values for 2019):

  Sum Value Sum Value previous month
Jan. 25 -
Feb. 36 25
Mar. 32 36
...    
Nov. 14 22
Dec. 52 14

 

This is my filter:

Thomas23_0-1636644929995.png

 

I use the formula

if(Month='Jan.', Sum({$<Year={$(vPrevYear)}, Month={'Dec.'}>} (Value)),
Above(Sum((Value))))

The info in the formula editor shows:

if(Month='Jan.', Sum({$<Year={2019)}, Month={'Dec.'}>} (Value)),
Above(Sum((Value))))

Do you have any idea how I can get the value for december 2019?

Thank you,
Thomas

1 Solution

Accepted Solutions
Thomas23
Contributor II
Contributor II
Author

Hi Abhijit,

now I solved it this way:
I created a new table with the sum of my column. Another column is the key field where the month was increased by 1:


[FACT_Data_aggr]:
Load
Sum(Value) as SumValue_PrevMonth,
Month(AddMonths(Date, 1))&'_'& Year(AddMonths(Date, 1)) AS Key
Resident [FACT-Data]
Group By Month(AddMonths(Date, 1))&'_'& Year(AddMonths(Date, 1));

Thank you for your support,
Thomas

View solution in original post

4 Replies
abhijitnalekar
Specialist II
Specialist II

Hi @Thomas23 ,

Please create another column in the script with the previous function.

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
Thomas23
Contributor II
Contributor II
Author

Hi Abhijit,

do you mean in the load script in the data editor?

 

Regards,

Thomas

abhijitnalekar
Specialist II
Specialist II

Yes @Thomas23 

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
Thomas23
Contributor II
Contributor II
Author

Hi Abhijit,

now I solved it this way:
I created a new table with the sum of my column. Another column is the key field where the month was increased by 1:


[FACT_Data_aggr]:
Load
Sum(Value) as SumValue_PrevMonth,
Month(AddMonths(Date, 1))&'_'& Year(AddMonths(Date, 1)) AS Key
Resident [FACT-Data]
Group By Month(AddMonths(Date, 1))&'_'& Year(AddMonths(Date, 1));

Thank you for your support,
Thomas