Solved: Re: How to get next 12 months data? - Qlik Community

Report Inappropriate Content · ‎2014-10-31

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

jagan · ‎2014-10-31

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.

View solution in original post

Report Inappropriate Content · ‎2014-10-31

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

Report Inappropriate Content · ‎2014-10-31

using set analysis

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

Report Inappropriate Content · ‎2014-10-31

Hi,

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

Report Inappropriate Content · ‎2014-10-31

Hi,

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

Report Inappropriate Content · ‎2014-10-31

Yes. You can do that using the Set Analysis.

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

Report Inappropriate Content · ‎2014-10-31

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

Report Inappropriate Content · ‎2014-10-31

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

Report Inappropriate Content · ‎2014-10-31

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

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

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

Report Inappropriate Content · ‎2014-10-31

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