Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
tan_chungkam
Contributor

Set Analysis

Hi all,

I have a sample excel file with contain 2 years of Margin figure.

I wanna get a accumulated margin from the beginning of the year till the latest month period.

The box current showing the total of whole year 2017 Margin which is wrong.

It should be 550 (a sum up of 201701-201704).

For example, now the current year month is 201804. The box should show the sum margin figure of 201701-201704.

When i filter it to 201803, then the box should show the sum margin figure of 201701-201703.

Hope you guys could understand what i mean.

Please find attached as my all required file.

 

11 Replies
rogerpegler
Contributor

Re: Set Analysis

I haven't checked what you already have in the qvf, but in the load script I would split the month into two fields, year and month.  Then you could have :

For Selected Year : sum({<Year={"=max(Year)"}, Month={"<=Max(Month)"}>}Margin)

For Prior Year : sum({<Year={"=max(Year)-1"}, Month={"<=Max(Month)"}>}Margin)

(note syntax may not be exactly correct!)

tan_chungkam
Contributor

Re: Set Analysis

Hi @rogerpegler ,

It doesn't right.

For prior year it will showing the sum up figure of 201701-201712.

Another problem is when i filter the year and period the figure just show the selected period instead of a sum up of certain period to another period.

Hope you could understand my explanation.

rogerpegler
Contributor

Re: Set Analysis

Hi @tan_chungkam 

Re Prior Year - if each month is tagged as 1 through 12, the effect of the Month={"<=Max(Month)"} is to only include months up to the selection - for example selecting 201803 should make Month = 3 and therefore the set analysis include Months 1, 2 and 3.  That's why you also need Year in the set analysis so that you only get one year's values and not multiple years added together! 

If it's not working, the syntax is probably not correct and therefore not having any impact.

Re only showing selected period, you should also get the set analysis to ignore the field being selected. I should have included that before.  For example:

sum({<Year={"=max(Year)"}, Month={"<=Max(Month)"},SelectedField=>}Margin)

pradeepreddy
Valued Contributor II

Re: Set Analysis

I don't have qliksense.. So did not opened the app.

As per my understanding...

Script: Load  Year, Month , Sales From DB_Table;

Chart : Straight Table

Dimension : Month

Expressions..

1) Sales (Up to max selected month) : Sum({<Year={'$(=max(Year))'}, Month={'<= $(=max(Month))'}>}Sales)

2) Accumulated Sales: rangesum(above(Sum({<Year={'$(=max(Year))'}, Month={'<= $(=max(Month))'}>}Sales),0, RowNo()))

Channa
Contributor III

Re: Set Analysis

RangeSum(Above(Sum(Sales), 0, RowNo()))  

 

for cumulative 

Channa
tan_chungkam
Contributor

Re: Set Analysis

Hi @Channa ,

 

Thanks for the advice.

It is accumulative but it isn't based on year.

And even after i put in Year set analysis but when i filter the year and period it doesn't correct figure.

Channa
Contributor III

Re: Set Analysis

Please share screen how it is showing and what you look for
Channa
tan_chungkam
Contributor

Re: Set Analysis

Hi @Channa ,

image.pngThis is the full data.

image.pngthis is when using [RangeSum(Above(Sum(Sales), 0, RowNo())) ]. Which is wrong. It should show 450 (sum up from 201801-201804).image.pngThis is when i use [sum({<Year={'$(vMaxYear)'}>}Margin)]. It is correct.image.pngIt should be 450 when i filter Year (2017) and Period (4)

Channa
Contributor III

Re: Set Analysis

ok

i put the expression for running total not for year

 

if you want to exclude period

=sum ( {<Period=, Year={'$(vMaxYear)'}>}Margin)

your box will show value for year even yo select period

Channa