50 Replies Latest reply: Mar 1, 2018 9:11 AM by nagamani vadisala

# 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.

• ###### Re: how to get amount in last 6 months and last 18 months

You're a little sparse on details but this might help...

In your load script, assuming you have a field called date, determine boundary dates:

let mostRecentDate = date#(max(date),'YYYYMMDD');

let mostRecentDate = mostRecentDate-day(mostRecentDate)+1;  // remove days

let mostRecent6Months = addMonths(mostRecentDate ,-6);

let mostRecent18Months = addMonths(mostRecentDate ,-18);

in your visualizations you can use the variables in set analysis:

sum({<date={'<\$(mostRecent6Months)'}>} value)

sum({<date={'<\$(mostRecent18Months)'}>} value)

sum({<date={'>=\$(mostRecent18Months)'}>} value)   // for oldest 6 months assuming only 24 months of data.

• ###### Re: how to get amount in last 6 months and last 18 months

here

i need to compare  sum of value between last 6 months of last 24 months and first 18 months of same 24 months.

how we can do that

can you suggest

• ###### Re: how to get amount in last 6 months and last 18 months

Can you share some Data to work with

Regards

• ###### Re: how to get amount in last 6 months and last 18 months

Hi,

Data is like

date                 data

20160228        100

20160728        100

20160928        100

20170101         100

20180228        100

• ###### Re: how to get amount in last 6 months and last 18 months

Hi,

if you already used Set Analysis, try this:

for last 6 months:

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

for last 18 months:

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

• ###### Re: how to get amount in last 6 months and last 18 months

the above solution is incomplete because it won't work if you don't select a date,

so here is the new one:

for last 6 months:

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

for last 18 months:

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

• ###### Re: how to get amount in last 6 months and last 18 months

here , with this i am getting same value for both statements, where i have different dates.

Can you please create app , wher ewe have 24 months and with data.

where we should get sum of data for 6 months and first 18 months and we have to get different values

• ###### Re: how to get amount in last 6 months and last 18 months

here it is,

you have the data, the expression, the output, enjoy

• ###### Re: how to get amount in last 6 months and last 18 months

where is output

• ###### Re: how to get amount in last 6 months and last 18 months

Can't you see this on the app I gave you ???

• ###### Re: how to get amount in last 6 months and last 18 months

In the app you sent have only sample date and data  which i provided. No Expression is there for 6 months and 18 months data. Can you please add that in app and resend please

• ###### Re: how to get amount in last 6 months and last 18 months

and also in screenshot, you provided 18 months as last 18 months but my request to get first 18 months in last 24 months

• ###### Re: how to get amount in last 6 months and last 18 months

One way is to change the date format

=Date(Date#(testdate,'YYYYMMDD'),'MM/DD/YYYY')

• ###### Re: how to get amount in last 6 months and last 18 months

Yap only thing can be added in

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

• ###### Re: how to get amount in last 6 months and last 18 months

i did not see app attached here

• ###### Re: how to get amount in last 6 months and last 18 months

here it is

here it is attached one more time

• ###### Re: how to get amount in last 6 months and last 18 months

yeah, i see it now.

But In the app you sent have only sample date and data  which i provided. No Expression is there for 6 months and 18 months data. Can you please add that in app and resend please

• ###### Re: how to get amount in last 6 months and last 18 months

OK, open the app, take screen shots of what you can see on it (Tables, and expressions on the tables).

If you don't know how to attach screen shots here, here is my email: youssefbelloumtn@gmail.com

JUST SHOW ME WHAT YOU CAN SEE ON THE APP I SEND TO YOU

Thank you

• ###### Re: how to get amount in last 6 months and last 18 months

Not able to attach screenshot now, but in app we have only 2 columns date and data

where i add date and data into sheet it gave as

date                                                 data

20160301                                      100

20160401                                       400

20160501                                       500

20160601

20160701

20160801

20160901

20161001

20161101

20161201

20170101

20170201

20170301

20170401

20170501

20170601

20170701

20170801

20170901

20171001

20171101

20171201

20180101

20180201

but i need an expression for last 6 months and first 18 moths in last 24 months. can  you help here please

• ###### Re: how to get amount in last 6 months and last 18 months

Trust me, everything is in front of you..

Please can anyone can help me here ? Maybe I'm missing something

Anyone to download the app and test it please ?

• ###### Re: how to get amount in last 6 months and last 18 months

in app where you have added expressions for 6 and 18 months data. have only below code even at data loader

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='fr-FR';

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

SET LongDayNames='lundi;mardi;mercredi;jeudi;vendredi;samedi;dimanche';

last_n_months:

LOAD * Inline [

date,data

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

];

• ###### Re: how to get amount in last 6 months and last 18 months

These are the expressions i am able to see from the app sent by yousseff.

For 6 months:

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

for 18 months:

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

I think this is the one you are looking for.

• ###### Re: how to get amount in last 6 months and last 18 months

here for 18 months , using this we will be brininging out last 18 months, but we need t get first 18 months in last 24 months

• ###### Re: how to get amount in last 6 months and last 18 months

so here is what you're looking for:

• ###### Re: how to get amount in last 6 months and last 18 months

Sorry, take this one (for first 18 months on the last 24 months)

• ###### Re: how to get amount in last 6 months and last 18 months

this worked for 18 months

can you give me the expression for last 6 months also please.

When i used expression which provided above seems to be having some issue

• ###### Re: how to get amount in last 6 months and last 18 months

Please precise one last time:

you want LAST 6 months of the last 24 months

or

you want FIRST 6 months of the last 24 months

?

• ###### Re: how to get amount in last 6 months and last 18 months

Last 6 months

if date have today's data as latest, we need to get 6 months from today to back

• ###### Re: how to get amount in last 6 months and last 18 months

for this one, I give you the same I gave you above:

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

• ###### Re: how to get amount in last 6 months and last 18 months

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)

• ###### Re: how to get amount in last 6 months and last 18 months

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

• ###### Re: how to get amount in last 6 months and last 18 months

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

• ###### Re: how to get amount in last 6 months and last 18 months

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

• ###### Re: how to get amount in last 6 months and last 18 months

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

• ###### Re: how to get amount in last 6 months and last 18 months

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

• ###### Re: how to get amount in last 6 months and last 18 months

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?

• ###### Re: how to get amount in last 6 months and last 18 months

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

• ###### Re: how to get amount in last 6 months and last 18 months

So, something like this

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)

• ###### Re: how to get amount in last 6 months and last 18 months

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

• ###### Re: how to get amount in last 6 months and last 18 months

I don't think I understand your requirement... but are you checking this WELL, NOT WELL for any specific dimension? Like for example Product wise check if it is WELL or NOT WELL? I suggest you to provide more information as we cannot keep on guessing about what you might need

• ###### Re: how to get amount in last 6 months and last 18 months

We are calculating sum of 18 months and 6 months right above.

If sum of 6 months is greater than 18 months i should have value as 'WELL' and if not "NOTWELL" and  should display this also at report beside the values of 6 and 18 months

• ###### Re: how to get amount in last 6 months and last 18 months

In a text box? In a chart with a dimension or multiple dimensions? You are still not helping us with your description... please provide more info

• ###### Re: how to get amount in last 6 months and last 18 months

At report we are showing date,6 months data,18 months data

Now we have add a text column based on 6 and 18 months data and show report as

date,text column,6 months data,18 months data

text column condition will be as If sum of 6 months is greater than 18 months i should have value as 'WELL' and if not "NOTWELL" and  should display this also at report beside the values of 6 and 18 months

is this help full

• ###### Re: how to get amount in last 6 months and last 18 months

You can try this

Aggr(

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')

, [Text Column])

• ###### Re: how to get amount in last 6 months and last 18 months

getting blank

• ###### Re: how to get amount in last 6 months and last 18 months

Can you share a sample or at least images of what you are seeing?

• ###### Re: how to get amount in last 6 months and last 18 months

i am getting blank

i tried this to use it as a dimension

at report, i am seeing

columns

Date            6monthsdata                        18 monthsdata

20170830    10                                             0

i need to see as

Date               TEXTCOLUMN             6monthsdata                        18 monthsdata

20170830                 WELL                                 10                                             0

But getting as

Date               TEXTCOLUMN             6monthsdata                        18 monthsdata

20170830                                               10                                             0

Aggr(

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')

, [Text Column])

• ###### Re: how to get amount in last 6 months and last 18 months

Sorry my friend, I don't think I understand. May be I am not the right person for this. youssef - Thanks for calling my name out, but I don't think I can help.

Best,

Sunny

• ###### Re: how to get amount in last 6 months and last 18 months

Thank you anyway Sunny.. Me too I'm done from it.

Good luck bro

• ###### Re: how to get amount in last 6 months and last 18 months

Thank you for your help sunny and youusef.

i got it