topic Re: Based on selection Above all months data in QlikView

Based on selection Above all months data

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

Hi Experts,

Here i want the Sum(sales) based on month selection with above all months

For example:-

My variable like

Variable3=MonthName(max(Date))

expression:-

=sum({<Month={'$(Variable3)'}>}Sales)

Here if i select may month its showing may month data its fine.

my requirement is:- if i select APRIL i want data from April,Mar,Feb,Jan

for that i have written expression like this

=sum({<Month={'<=$(Variable3)'}>}Sales(here i am getting result is Zero)

is there any mistake in expression ,please let me know.

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 10:36:56 GMT

Hi Experts ,

Help me on that loveisfail‌vinieme12‌swuehl‌@

Re: Based on selection Above all months data

Anil_Babu_Samineni — Thu, 18 May 2017 10:41:27 GMT

Expression seems simple only, Would you able to provide sample to check

Let me explain little more how it will work

sum({<Month={">=$(=AddYears(Max(Month),-4)) <=$(=Max(Month))"}>}Sales)

PS - This will return when you click the month for April this will return as April, March, Feb, Jan.

The same way will work for Date as well if Date format is bit clear to understand each together

sum({<Date={">=$(=AddYears(Max(Date),-4)) <=$(=Max(Date))"}>}Sales)

Re: Based on selection Above all months data

Kushal_Chawda — Thu, 18 May 2017 10:43:36 GMT

How values in Month field looks like?

Re: Based on selection Above all months data

prieper — Thu, 18 May 2017 10:49:44 GMT

Depends a bit on your formatting of data,

basically your expression is correct, if you replace the ' with "

Suggest to try first with pure numerics.

Peter

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 10:49:57 GMT

Hey Anil,

thanks,

Here i have four years of data 2012 to 2015, if i select Nov 2015 ,

it was showing Nov 2015 to Jan 2012 i don't want like that

if i select Nov 2015 ,i want here from Nov 2015 to Jan 2015 sales only.

May i know Why My Expression is Not working

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 10:51:08 GMT

Hi Bro,

Monthname like

Jan 2012

Nov 2015

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 10:52:06 GMT

Hi Brother,

I tried with " "

its not working

Re: Based on selection Above all months data

Anil_Babu_Samineni — Thu, 18 May 2017 10:53:14 GMT

Cool, Now you need to sort by help of YearStart. Make sure to use Date Format in case any weird happens

sum({<Month={">=$(=YearStart(Max(Month))) <=$(=Max(Month))"}>}Sales)

Re: Based on selection Above all months data

vinieme12 — Thu, 18 May 2017 10:54:34 GMT

see this post

Calculating rolling n-period totals, averages or other aggregations

Accumulative Sums

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 10:55:41 GMT

Good Anil, its working

but Why My expression is not working .

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 10:59:20 GMT

Hi Bro,

Its not working ,The main thing is We are not able to check the Accumulation in Pivot Table.

Re: Based on selection Above all months data

Anil_Babu_Samineni — Thu, 18 May 2017 10:59:26 GMT

The main reason is you are en-counting the sum for Total Where Maximum date

<= Max(Month) -- Returns from Max date to Min Date

When we restrict Something in >= Condition it will consider as Min to Max. Here, Set analysis work as Conditional like >= and <= Condition

Re: Based on selection Above all months data

Kushal_Chawda — Thu, 18 May 2017 11:00:53 GMT

if it's working then can' t you use this expression?

Re: Based on selection Above all months data

Kushal_Chawda — Thu, 18 May 2017 11:03:31 GMT

try with this

sum({<Month={'<=$(Variable3)'},Month>}Sales)

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 11:11:10 GMT

Not working Brother,

It was showing all months data

Re: Based on selection Above all months data

Kushal_Chawda — Thu, 18 May 2017 11:33:17 GMT

would you be able to share the sample?

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 11:43:50 GMT

Hi Bro,

PFA

Re: Based on selection Above all months data

qlikview979 — Thu, 18 May 2017 18:53:29 GMT

Hi Anil,

This is good

sum({<Month={">=$(=YearStart(Max(Month))) <=$(=Max(Month))"}>}Sales) ( if i am select 2017 May its showing May and above all months)

The main thing if i select 2017 may ,i want to show 2017 may and above data in one column and 2016 may and above data in one column .

Output:-

Ex:-(if i select May 2017)

Year, Monthname 2016 2017

Jan2016 100 0

Feb2016 200 0

2016 Mar2016 300 0

April2016 400 0

May2016 500 0

Jan2017 0 600

Feb2017 0 700

2017 Mar2017 0 800

April2017 0 900

May2017 0 1000

if i select June i want to show both years from June to January.

Regards,

Mahesh

Re: Based on selection Above all months data

ziadm — Thu, 18 May 2017 19:14:49 GMT

sum( aggr( rangesum( above( sum( {1<MonthName>} Sales),0,3) ), MonthName))