topic Re: rolling 6 months in QlikView

rolling 6 months

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have used the below expression to get the resuts as per business rules.

We got a requirement that we need to show the results roll banck to 6 months.

Plase help me how to get the results.

Logic used : = IF(isnull(num(sum({$<[Month Abbr]=, Year=, [Quarter Name]=, [Calendar Date]={'$(=$(TestDate))'}>}[LS Open]),'#,##0')),

Null(),

Num( num(sum({$<[Month Abbr]=, Year=, [Quarter Name]=, [Calendar Date]={'$(=$(TestDate))'}, [LS Age ]={1}>} [LS Open]),'#,##0')

num(sum({$<[Month Abbr]=, Year=, [Quarter Name]=, [Calendar Date]={'$(=$(TestDate))'}>}[LS Open]),'#,##0'),'#,###0.0%'))

where $(TestDate) = Date(Addmonths(max({$<[# QN Open]={1}>}[Calendar Date]), -1, 1), 'DD-MMM-YYYY')

Thanks..

YoussefBelloum — Wed, 16 May 2018 10:01:18 GMT

Hi,

to be able to rollback using set analysis, yo have two options:

1. use Month_year or Year_month field

2. use Date field

in your actual expression, you're detaching Year and Month fields from the expression and you're setting a specific date.

So, to be able to roll back, I think you can use your date field, but remove that variable and replace it with something like:

[Calendar Date]={'>$(=AddMonths(max([Calendar Date]),-6))<=$(=max([Calendar Date]))'}

arvind_patil — Wed, 16 May 2018 10:06:10 GMT

Hi nareshthavidishetty ,

Please use below code and convert your expression like below.

Sum({<DateField={">=$(=addmonths(max(DateField),-5))<=$(=max(DateField))"},Year=,Month=,Quarter=>}Sales)

Thanks,

Arvind Patil

nareshthavidishetty — Wed, 16 May 2018 10:46:52 GMT

Hi,

Thanks for the inputs..It's working fine.

But i need to display the in between periods as well like columns july 2017,aug 2017.....

With the approaches as suggeted in earlier,it will show only 6th month.ut ineed tomshow in between months as well.

Rules : Table straight table.Already having another dimension.

Thanks..