Set Analysis

[tan_chungkam]

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.

[rogerpegler]

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]

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]

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]

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]

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

for cumulative 

[tan_chungkam]

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]

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

[tan_chungkam]

Hi @Channa ,

This is the full data.

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

[Channa]

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