topic Re: last 3 months data in QlikView

last 3 months data

manoranjan_d — Thu, 10 Nov 2016 14:44:26 GMT

hi all,

i m getting wrong output for past three months data, pls help me on this

x1 has to get 4

x2 has to get 1

x3 has to get 2

where it shd avoid the current month count(november) and it shd get the last three months count for the login history based on the username dimension

pls help me on the wt is correct expression for this?

USERNAME	LOGIN HISTORY
x1	1/1/2016
x1	2/1/2016
x1	3/1/2016
x1	4/1/2016
x1	5/1/2016
x1	6/1/2016
x1	7/1/2016
x1	8/1/2016
x1	9/1/2016
x1	10/1/2016
x1	10/2/2016
x2	1/1/2016
x2	2/1/2016
x2	3/1/2016
x2	4/1/2016
x2	5/1/2016
x2	6/1/2016
x2	7/1/2016
x2	8/1/2016
x3	9/1/2016
x3	10/1/2016
x3	11/1/2016
x3	1/1/2016
x3	2/1/2016
x3	3/1/2016
x3	4/1/2016

Re: last 3 months data

Gysbert_Wassenaar — Thu, 10 Nov 2016 14:50:55 GMT

Create a flag field in the script:

LOAD

USERNAME,

InQuarter ([LOGIN HISTORY], MonthStart(Today()-1)) * -1 as InLastQuarter,

...other fields...

FROM

...source-data

;

Then create chart with USERNAME as dimension and sum the values of InLastQuarter in your chart expression: sum(InLastQuarter)

Re: last 3 months data

manoranjan_d — Thu, 10 Nov 2016 16:45:03 GMT

in the script i shd always the base data, ony in the front end i have to show the last three months data

Re: last 3 months data

johnw — Thu, 10 Nov 2016 16:57:40 GMT

Perhaps this, though I prefer my complexity in script rather than in charts where practical.

count({<[LOGIN HISTORY]={"=[LOGIN HISTORY]>=monthstart(today(),-3) and [LOGIN HISTORY]<monthstart(today())"}>} distinct [LOGIN HISTORY])

Re: last 3 months data

Gysbert_Wassenaar — Fri, 11 Nov 2016 06:34:38 GMT

I'm suggesting you add a field in the script. I'm not telling you to remove anything. So you will always have the base data. And you'll only limit what you show in the front end with an expression.