Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
Can you share some Data to work with
Regards
Hi,
Data is like
date data
20160228 100
20160728 100
20160928 100
20170101 100
20180228 100
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
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)
One way is to change the date format
=Date(Date#(testdate,'YYYYMMDD'),'MM/DD/YYYY')
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)
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
Yap only thing can be added in
sum( {< date={">=$(=Date(AddMonths(Max(Date#(date,'YYYYMMDD'))),-6),'YYYYMMDD'))<=$(=Date(Date#(date,'YYYYMMDD'),'YYYYMMDD'))" }>} data)