Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

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.

1 Solution

Accepted Solutions
Champion
Champion

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


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

View solution in original post

51 Replies
Partner
Partner

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.

Specialist
Specialist

Can you share some Data to work with

Regards

Creator
Creator

Hi,

Data is like

date                 data

20160228        100

20160728        100

20160928        100

20170101         100

20180228        100

Creator
Creator

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

Champion
Champion

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)


Specialist
Specialist

One way is to change the date format

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

Champion
Champion

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)


Creator
Creator

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

Specialist
Specialist

Yap only thing can be added in

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