counting values in previous months

[pgonin]

Hello dears!

May I ask you a pretty (hopefully) simple question.

Let's say I have a model consisting of period and id, like this:

period	id
2019-09	a
2019-09	b
2019-08	c
2019-08	d
2019-07	e
2019-07	f
2019-07	g
2019-06	h

And then, on a new app on a new sheet I want a pivot table with period as a dimension and count of id's for 3 previous periods as a measure: like this as a result

period	count of ids for 3 previous periods
2019-09	7
2019-08	6
2019-07	4
2019-06	1

How would one do so?

I don't want it to do in the load editor as this is a simple example, actual values rely on other filtered values, etc.

[Kushal_Chawda]

Data:
LOAD
date(Date#(period,'YYYY-MM'),'YYYY-MM') as period,
id
FROM [lib://Qlik ]

If you already formatted your period then don't need to perform above step. You can directly use below expression

=count({<period,Year,Month,period ={">=$(=date(addmonths(max(period),-3),'YYYY-MM'))<=$(=date(max(period),'YYYY-MM'))"}>}id)

[pgonin]

Thank you. But it still shows me the values for current month only (previous months aren't counted). Please look carefully on my answer in example (what I intend to receive as output).

Also, I meant 3 previous months including current, so for 2019-09 it should be calculated for 2019-09,2019-08 and 2019-07.

Again, thank you and looking forward to receiving a solution.

[martynova]

I did everything in script. And it looks like you want (see in attached picture).

[raw]:
LOAD
date#(data, 'YYYY-MM') as raw_date,
value as raw_value
FROM [lib://AttachedFiles/test.xlsx]
(ooxml, embedded labels, table is Лист1);

NoConcatenate
[group]:
LOAD
raw_date as group_date
, count(raw_value) as group_value
resident raw
GROUP BY raw_date
ORDER BY raw_date asc
;

[date_as]:
LOAD DISTINCT group_date as date_as
, group_value as value_as
resident group;

left join (group)
LOAD date_as
, value_as
resident date_as;

DROP TABLE date_as;

[final]:
LOAD group_date as final_date
, sum(value_as) as final_value
resident group
where date_as<=group_date
GROUP BY group_date;