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: 
Anonymous
Not applicable

how to get amount in last 6 months and last 18 months

i am stuck here for calculating data

From last 24 months have to check data with first 6 months and

for last 6 months based on a date column in format(YYYYMMDD) to first 18 months.

Can you anyone please suggest.

51 Replies
Anonymous
Not applicable
Author

but when i am using this, i am getting 18 months of data. this working same as 18 months statement you provided me

sum( {< date={">=$(=Date(AddMonths(Date#(Max(date),'YYYYMMDD'),-6),'YYYYMMDD'))<=$(=Date(Date#(Max(date),'YYYYMMDD'),'YYYYMMDD'))" }>} data)

Anonymous
Not applicable
Author

hmm actually i over looked 18 months logic also. it is also not working prroperly

YoussefBelloum
Champion
Champion

Sorry nagamani, maybe stalwar1‌ have time to take a look here and give you something else

Anonymous
Not applicable
Author

OK, THANK YOU for all you support.

I got logic for 6 months but waiting on to get first 18 months in last 24 months

sunny_talwar

Wow!! this is one long thread, somebody can brief me on what is going on

Anonymous
Not applicable
Author

HI lalwar,

We need to get to create as dimension, where we have to compare sum of last 6 months data greater than sum of first 18 months data in last 24 months and display as Well if greater than or "notwell" is it is less than.

In part of that we are trying to get sum of data for first 18 months from last 24 months, where i am facing issue. Can you please help

we have data as below

20160301,100

20160401,100

20160501,100

20160601,100

20160701,400

20160801,500

20160901,100

20161001,100

20161101,100

20161201,100

20170101,400

20170201,500

20170301,100

20170401,100

20170501,100

20170601,100

20170701,400

20170801,500

20170901,100

20171001,100

20171101,100

20171201,100

20180101,400

20180201,500

sunny_talwar

So, based on this input data that you just provided, what will be the expected output in numerical terms you expect to get from it?

Anonymous
Not applicable
Author

i should get date and data as output

dates: should have first 18months dates from last 24 months

sum of data: related to 18 months

dates: should have last 6 months

sum of data: related to 6 months

If we get data for above i will take of others which i need. to move forward i need this

sunny_talwar

So, something like this

Capture.PNG

In order to do this, you need to first make sure that QlikView or Qlik Sense is reading your date correctly as a date field. To do this, I had to do this in the script

Table:

LOAD Date(Date#(Date, 'YYYYMMDD')) as Date,

Value;

LOAD * INLINE [

    Date, Value

    20160301, 100

    20160401, 100

    20160501, 100

    20160601, 100

    20160701, 400

    20160801, 500

    20160901, 100

    20161001, 100

    20161101, 100

    20161201, 100

    20170101, 400

    20170201, 500

    20170301, 100

    20170401, 100

    20170501, 100

    20170601, 100

    20170701, 400

    20170801, 500

    20170901, 100

    20171001, 100

    20171101, 100

    20171201, 100

    20180101, 400

    20180201, 500

];

Once you do this, the expressions are fairly simple

=Sum({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"}>}Value)

=Sum({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -5)) & '<=' & Date(AddMonths(Max(Date), 0)))"}>}Value)

Anonymous
Not applicable
Author

thankyou,i am to get values as expected

i tried to create one dimension at report as

if(Sum({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"}>}Value)

> Sum({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -5)) & '<=' & Date(AddMonths(Max(Date), 0)))"}>}Value), 'WELL','NOTWELL')


i am getting as invalid dimension, can you help me on this please