Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
DS14
Partner - Contributor III
Partner - Contributor III

Getting Values More than equal to Total

Hi Experts,

I have calculated RM% . Now my requirement is based on selection the records which has the value greater than equal to total RM% on visible. For Example

In below image my Avg. RM% is 69.6% and now I want only those records in my table whose RM% Value is greater than equal to  69.6%.

Like from the image 82.6%,71.9%,339.5% and all.

Please help if anybody can!

Thanks in advance.

15 Replies
DS14
Partner - Contributor III
Partner - Contributor III
Author

As per below sample I got the total RM% 74.0%, Now I need only those records in the table which has the RM% values greater than equal to Total RM% Value i.e more than equal to 74.0%.

like 87.2%, 91.5%,374.1%,103.9% etc.

I think this will give you the clear picture.

PlantCodeMaterial NoFiscal YearFiscal MonthBOMBase Price[Billing Qty]RM%
Total------74.0%
BP0XBP021000001332432018-2019Sep6.691083.61200.0%
BP0XBP021000001362732018-2019Sep0.216832.842405.0%
BP0XBP021000002121022018-2019Sep28.458807.427087.2%
BP0XBP021000002121032018-2019Sep47.2613426.426091.5%
BP0XBP021000002131022018-2019Sep28.458807.427087.2%
BP0XBP021000002131032018-2019Sep47.2613426.426091.5%
BP0XBP021000002560562018-2019Sep0.131160200022.4%
BP0XBP021000002560602018-2019Sep0.394332120010.8%
BP0XBP021000002560612018-2019Sep0.52801.624015.6%
BP0XBP021000002561102018-2019Sep1.673036120066.0%
BP0XBP021000002570132018-2019Sep4.7843709500.0%
BP0XBP021000002570132018-2019Sep4.783450750374.1%
BP0XBP021000002570132018-2019Sep4.7823005000.0%
BP0XBP021000002570142018-2019Sep4.784025875103.9%
BP0XBP021000002570332018-2019Sep7.15151.2940129.6%
BP0XBP021000002570352018-2019Sep3.236330100098.7%
pradosh_thakur
Master II
Master II

Yeah ..

1: what total function are you using ?

 

if you change rm% code to below does the output you are seeing now change ?

(


Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,Procurement = {'E'}
,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}[Billing Qty])
*
Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Costing Variant] = {'YPC1'}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}BOM ))

/
Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,Procurement = {'E'}
//,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}[Base Price])

Learning never stops.
DS14
Partner - Contributor III
Partner - Contributor III
Author

Yes, Its changed.
pradosh_thakur
Master II
Master II

An image for that plesase. You didn't answer what total function are you using ?
Learning never stops.
DS14
Partner - Contributor III
Partner - Contributor III
Author

Hi Pradosh,

New RM.PNG

This is the new output  I am getting using the logic without aggr function.

No total function I used for calculation RM%, The logic I mentioned before is the only I used for calculating RM%.

sunny_talwar


@DS14 wrote:

Hi Sunny it's not working for me,

I am calculating RM% using the below expression.

(

sum(
aggr(Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,Procurement = {'E'}
,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}[Billing Qty]),PlantCode,[Material No],[Fiscal Month])
*
aggr(Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Costing Variant] = {'YPC1'}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}BOM),PlantCode,[Material No],[Fiscal Month])
)
/
Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,Procurement = {'E'}
//,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}[Base Price])

)

 

If i am using this expression in the way you suggested its not working.

Please suggest something on this.


In your earlier post you mentioned RM% was this

(sum(billing qty)*sum(material)) / sum(Billin Price)

Is this simplified version of the above expression or did the expression change? Either ways, it might help if you are able to share a sample qvf for us to take a look at.