Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table :
test:
vendor name,
Sales Value,
6 Months Sales Value
from QVD;
what I have :
Vendor Name | 6 Months Sales Value (Total = 9000) |
B | 3000 |
A | 5000 |
C | 1000 |
What I need : for eg : if vendor A [Cumulative Sum] (high to low depend on 6months Sales value)< Sum(Total [Cumulative Sum])*.8
Top Vendor Name | 6 Months Sales Value |
A | 5000 |
I only need top Vendors Name in the table which have :
[Cumulative Sum] (high to low depend on 6months Sales value)< Sum(Total [Cumulative Sum])*.8 then [Top Supplier] = "Y"
Requirement: is it possible to do all in load script to get top vendors name?
1- Look at Vendor Name in the last 6 months and calculate the total (9000) at the Vendor level, order that table from high to low. (front end)
2- Then create a cumulative sum (high to low depend on 6months Sales value)(so Vendor#1 shows Vendor 1 (6 Months Sales Value), Vendor #2 shows Vendor #1 AND Vendor #2 (6 Months Sales Value), and so on).
Whenever [Cumulative Sum] (high to low depend on 6months Sales value)< Sum(Total [Cumulative Sum])*.8 then [Top Supplier] = "Y"
Sum(Total [Cumulative Sum]) === [Cumulative Sum] (high to low depend on 6months Sales value)/ Total(9000)
@Sara_3 try below
=sum({<Vendor={"=sum(aggr(rangesum(above(Sum([6 Months Sales Value]),0,RowNo())),(Vendor,(=Sum([6 Months Sales Value]),DESCENDING))))/ sum(total [6 Months Sales Value])< 0.8"}>} [6 Months Sales Value])
@Sara_3 Yes. you can suppress the zero values from Add-Ons option
@Sara_3 try below
=sum({<Vendor={"=sum(aggr(rangesum(above(Sum([6 Months Sales Value]),0,RowNo())),(Vendor,(=Sum([6 Months Sales Value]),DESCENDING))))/ sum(total [6 Months Sales Value])< 0.8"}>} [6 Months Sales Value])
Thanks Kushal , I am going to try today and let you know .
Really Appreciated!
Kushal I tried your above logic it gives me numeric value , I need top Vendors names in my table who has under this criteria :
[Cumulative Sum] (Decending 6months Sales value)< Sum(Total [Cumulative Sum])*.8 then [Top Supplier] = "Y"/ OR "top vendors names"
My output should be:
Top Vendors Name ([Cumulative Sum] < Sum(Total [Cumulative Sum])*.8 | 6 Months Sales Value (higher to lower value) |
D | 5000 |
A | 3500 |
B | 1800 |
Thanks,
Beenish.
@Sara_3 it gives numeric value but that numeric values is for top vendor. You need to Use this measure in chart where dimension is Vendor
oh ok, now I added this measure with my Vendor Name dimension. it gives some vendors has 6 Months sale values in front of vendor and gives some of them 0 value means these are not top vendors under my criteria right?????
@Sara_3 Yes. you can suppress the zero values from Add-Ons option
Thanks!
Hi Kushal,
its working , but what i have to do when I am going to add 1 more column which is [Future 6 months sales value](field already exists) want to comparison with last 6 months. How to modify the BELOW expression for Future 6 Months sales without changing the Top vendors and last 6 months sales value (high to low) :
=sum({<Vendor={"=sum(aggr(rangesum(above(Sum([6 Months Sales Value]),0,RowNo())),(Vendor,(=Sum([6 Months Sales Value]),DESCENDING))))/ sum(total [6 Months Sales Value])< 0.8"}>} [6 Months Sales Value])
output :
Top Vendors name | 6 Months sales value (HIGH TO LOW) | Future 6 months sales value |
D | 5000 | |
A | 3500 | |
B | 1800 |
@Sara_3 try below
=sum({<Vendor={"=sum(aggr(rangesum(above(Sum([6 Months Sales Value]),0,RowNo())),(Vendor,(=Sum([6 Months Sales Value]),DESCENDING))))/ sum(total [6 Months Sales Value])< 0.8"}>} [Future 6 months sales value])