Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
tan_chungkam
Creator
Creator

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.

 

Labels (3)
11 Replies
rogerpegler
Creator II
Creator II

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
Creator
Creator
Author

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
Creator II
Creator II

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
Specialist II
Specialist II

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
Specialist III
Specialist III

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

 

for cumulative 

Channa
tan_chungkam
Creator
Creator
Author

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
Specialist III
Specialist III

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

Hi @Channa ,

This is the full data.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 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)'&#125;>&#125;Margin)]. It is correct.This is when i use [sum({<Year={'$(vMaxYear)'}>}Margin)]. It is correct.It should be 450 when  i filter Year (2017) and Period (4)It should be 450 when i filter Year (2017) and Period (4)

Channa
Specialist III
Specialist III

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