Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pgonin
Contributor II
Contributor II

counting values in previous months

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:

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

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

periodcount of ids for 3 previous periods
2019-097
2019-086
2019-074
2019-061

 

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.

Labels (1)
3 Replies
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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II

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;