topic How to get next 12 months data? in QlikView

How to get next 12 months data?

Fri, 31 Oct 2014 09:07:20 GMT

Hi,

I have a date field(year-month) and I want to get count of contracts for next 12 months on selecting any date.

How to do it?

Thanks

Saurabh

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:14:18 GMT

Saurabh,

Assuming you want to do this in a chart object, below is a sample expression.

Sum({$<[YearMonth]={'>$(=min([YearMonth])-1)<$(=min([YearMonth])+12'}>} Salary)

Note: Change the filed names according to your script field names.

Regards,

Murali

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:15:52 GMT

using set analysis

sum(<field = {'>=($(vSelectedDate))<=(addmonths($(vSelectedDate),12))'}>}Sales)

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:17:40 GMT

Hi,

I am selecting a date and on selection. the next 12 months should display in chart along with count of that particular months.

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:19:02 GMT

Hi,

I want next 12 months on dimension and corresponding count values on y-axis in the chart.

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:19:04 GMT

Yes. You can do that using the Set Analysis.

Can you upload the QVW, so that we can help you with the solution?

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:29:07 GMT

I am attaching the qvw. In it, I would like to display next 12 month's values on line chart.

Please send me the image of the solved one as I am having personal edition.

Thanks Saurabh

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:35:03 GMT

Hi Saurabh

as far as I understand you want to select a date (let's say Apr- 2013) and get for each next 12 month the result by month (so from Apr -2013 until March 2014)

So put as calculated dimension month like =if(month>=201304, month) -- use your date format

Expression is as usual sum(contracts)

it will display only 12 months
best regards

Chris

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:46:04 GMT

Add Month(OrderDate) as your dimension and below as expression.

count({1<[OrderDate]={">=$(=addmonths(monthend(max([OrderDate]))+1,-12))

<=$(=max([OrderDate]))"}>}quantityOrdered)

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:48:52 GMT

Hi,

If I select a month and compare it, then I will get that month only. How will I get next 12 months?

Thanks

Saurabh

Re: How to get next 12 months data?

Fri, 31 Oct 2014 09:51:39 GMT

Hi, But if we use 1 in the expression then it will show count for all the values, not for the selected values.

Re: How to get next 12 months data?

jagan — Fri, 31 Oct 2014 10:36:03 GMT

Hi,

Try like this

=count({<[orderDate]={">=$(=Date(monthstart(max([orderDate]), 1), 'M/D/YYYY hh:mm:ss TT'))

<=$(=Date(monthend(max([orderDate]), 12), 'M/D/YYYY hh:mm:ss TT'))"}>}quantityOrdered)

Regards,

Jagan.

Re: How to get next 12 months data?

jagan — Fri, 31 Oct 2014 10:38:40 GMT

Hi,

I think it is better to truncate the Time part in the date field since you are not using this, you can do this like below

LOAD comments,

customerNumber,

Date(orderDate) AS orderDate,

orderNumber,

requiredDate,

shippedDate,

status;

SQL SELECT comments,

customerNumber,

orderDate,

orderNumber,

requiredDate,

shippedDate,

status

FROM Orders;

=count({<[orderDate]={">=$(=monthstart(max([orderDate]), 1))

<=$(=monthend(max([orderDate]), 12))"}>}quantityOrdered)

Hope this helps you.

Regards,

Jagan.

Re: How to get next 12 months data?

its_anandrjs — Fri, 31 Oct 2014 11:14:17 GMT

Try this way also but in your data model the date in time stamp so in place of the use Date only for that in the load script add script for only date

LOAD comments,

customerNumber,

Date( [orderDate],'MM/DD/YYYY') AS [orderDate]

orderNumber,

requiredDate,

shippedDate,

status;

SQL SELECT comments,

customerNumber,

orderDate,

orderNumber,

requiredDate,

shippedDate,

status

FROM Orders;

And then in charts use below expression

Sum({< [orderDate] = {'>=$(=AddMonths(Max(Date),-12)) <=$(=Max(Date))'}>} quantityOrdered)

Because in the SET expression it create problems.

Regards

Anand

Re: How to get next 12 months data?

Sat, 01 Nov 2014 05:58:36 GMT

Hi Anand,

I am not getting the result as per your expression. It restricts me from January to selected order date.

But I have got the answer.

May be you would like to see-

=sum({<[orderDate]={">=$(=monthstart(max([orderDate]), 1))<=$(=monthend(max([orderDate]), 12))"}>}quantityOrdered)

Thanks

Saurabh