Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

manigunta
Contributor

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
Esteemed Contributor

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)


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

50 Replies
stascher
Contributor

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.

zebhashmi
Valued Contributor

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

Can you share some Data to work with

Regards

manigunta
Contributor

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

manigunta
Contributor

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

YoussefBelloum
Esteemed Contributor

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)


zebhashmi
Valued Contributor

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

YoussefBelloum
Esteemed Contributor

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)


manigunta
Contributor

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

zebhashmi
Valued Contributor

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)

Community Browser