Previous Month Data Generator in QlikSense

[Ron1]

I have a source table. In QlikSense there will be two filter panes (Year , MonthNumber)

There are below three requirements:

1) When there will be no selection the previous month(from current month) data will be visible.

2) If user selects some MonthNumber from Filter, in chart only previous month(selected month -1) data should be seen.

3) If user select '1' as MonthNumber, previous year December Data should be seen.

Product ID	Product Category	Year	Month	MonthNumber	Sales
1	Electronics	2018	Dec	12	123
2	Books	2019	Jun	6	1231
3	HomeStuffs	2017	Feb	2	22
4	Appliances	2019	Dec	12	32
5	Electrical	2019	Jan	1	12
6	Electronics	2016	Mar	3	13
7	Books	2017	Apr	4	54
8	Electronics	2019	May	5	32
9	Electronics	2017	Jan	6	14
10	Appliances	2019	Jul	7	12
11	HomeStuffs	2016	Dec	12	56

[Ron1]

Please treat below as source table

Product ID	Product Category	Year	Month	MonthNumber	Sales
1	Electronics	2018	Dec	12	123
2	Books	2018	Jul	7	1231
3	HomeStuffs	2017	Feb	2	22
4	Appliances	2019	Jun	6	32
5	Electrical	2019	May	5	12
6	Electronics	2016	Mar	3	13
7	Books	2018	Jun	6	54
8	Electronics	2016	May	5	32
9	Electronics	2017	Jan	1	14
10	Appliances	2018	Nov	11	12
11	HomeStuffs	2016	Dec	12	56

[Vegar · Accepted solution]

You will need to create a field that increase in value for each month, like YearMonth.  See an example on how to do it in my script below. 

(In my solution I assume that current month is equal to the latest year month in the data set. )

LOAD 
  [Product ID],
  [Product Category],
  Year,
  Month,
  monthname(MakeDate(Year, MonthNumber)) as YearMonth,
  //MonthNumber,
  Sales 
Inline [
Product ID,	Product Category,Year,Month,MonthNumber,Sales
1,			Electronics,	2018, Dec,	12,			123
2,			Books,			2018, Jul,	7,			1231
3,			HomeStuffs,		2017, Feb,	2,			22
4,			Appliances,		2019, Jun,	6,			32
5,			Electrical,		2019, May,	5,			12
6,			Electronics,	2016, Mar,	3,			13
7,			Books,			2018, Jun,	6,			54
8,			Electronics,	2016, May,	5,			32
9,			Electronics,	2017, Jan,	1,			14
10,			Appliances,		2018, Nov,	11,			12
11,			HomeStuffs,		2016, Dec,	12,			56];

When you have this field you can use it in a set expression in your application.

sum({<
	Year,  //ignore selections on year
	Month, //ignore selection in month
	YearMonth={'$(=monthname(max(YearMonth),-1))'} //YearMonth should be previous of selected max month.
	>}Sales)

This will give you this value selecting Jan, 2018 will give you these values were previous month is the expression above.

[Ron1]

Thank you very much. It worked..:)