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.

1 Solution

Accepted Solutions
YoussefBelloum
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
stascher
Partner - Creator II
Partner - Creator II

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.

zebhashmi
Specialist
Specialist

Can you share some Data to work with

Regards

Anonymous
Not applicable
Author

Hi,

Data is like

date                 data

20160228        100

20160728        100

20160928        100

20170101         100

20180228        100

Anonymous
Not applicable
Author

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

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


zebhashmi
Specialist
Specialist

One way is to change the date format

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

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


Anonymous
Not applicable
Author

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

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