Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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
Specialist

Hi @Thomas23 ,

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

Regards,
Abhijit
keep Qliking...
Thomas23
Contributor II
Contributor II
Author

Hi Abhijit,

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

 

Regards,

Thomas

abhijitnalekar
Specialist
Specialist

Yes @Thomas23 

Regards,
Abhijit
keep Qliking...
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