Discussion board where members can get started with Qlik Sense.
May I ask you a pretty (hopefully) simple question.
Let's say I have a model consisting of period and id, like this:
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
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.
Data:LOADdate(Date#(period,'YYYY-MM'),'YYYY-MM') as period,idFROM [lib://Qlik ]
If you already formatted your period then don't need to perform above step. You can directly use below expression
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.
I did everything in script. And it looks like you want (see in attached picture).
[raw]:LOADdate#(data, 'YYYY-MM') as raw_date,value as raw_valueFROM [lib://AttachedFiles/test.xlsx](ooxml, embedded labels, table is Лист1);
NoConcatenate[group]:LOADraw_date as group_date, count(raw_value) as group_valueresident rawGROUP BY raw_dateORDER BY raw_date asc;
[date_as]:LOAD DISTINCT group_date as date_as, group_value as value_asresident group;
left join (group)LOAD date_as, value_asresident date_as;
DROP TABLE date_as;
[final]:LOAD group_date as final_date, sum(value_as) as final_valueresident groupwhere date_as<=group_dateGROUP BY group_date;