31 Replies Latest reply: Feb 21, 2017 4:35 AM by Nagarjuna Vattikuti

# How to sort items based on spend and i need figures based on savings in the pie-chart

How to sort items based on spend and i need figures based on savings in the pie-chart.I want to sort only top 3 items and  this is my table

 Item ID Spend Savings 11 120 12 12 140 14 13 90 40 14 30 16 15 180 18 16 60 30 17 110 17 18 40 8 19 130 25 20 160 10

So pie-chart answer should be like this                                          and NOT like this

Item ID      Savings                                                                 Item ID      Savings

15                18                                                                          13             40

20                10                                                                          16             30

12                14                                                                          19             25

Regards

Nagarjuna

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

How do you get the top 3 to be 15, 20, 12?

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I think based on the Spend column

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

May be this:

If(Rank(Sum(Spend)) < 4, Sum(Savings))

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

or

Sum({<[Item ID] = {"=Rank(Sum(Spend)) < 4"}>}Savings)

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Thanks for your assistance, but both are not working.

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

You have any other formula?

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Seems to be working for me

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I have multiple spend amounts and multiple saving amounts for the same item, sorted top 3 items using table based on spend, so all spends and savings came  to one one number for one item, then i tried the above  formula but it is showing based on top 3 savings only. Is this because of multiple spends and savings?

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I don't think it should be a problem, but if you are able to share a sample where you see an issue, we can dig deeper into it

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

My Skype ID : nagarjuna000024@hotmail.com,

If you are free just send me the request we will discuss it. I will be available after one hr.

Thank you

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Unfortunately, I cannot... May be somebody else can offer assistance

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

=if(

Aggr(Rank(Sum(Item ID)), Spend)<=3,

Savings,

'Others'

)

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

This is my sample data, if u get this correctly then probably works for me. Please do this favor

 Item ID Spend Savings 5112 3000 1500 5112 20000 4000 5112 25000 3000 6113 34560 2000 7889 56000 4000 9887 670000 6000 6113 34520 4000 9887 120000 1200 7889 3000 1000 5112 75000 12000 7889 10000 2000 9887 14000 1400 6113 98760 20000 3456 34560 10000 6113 100000 30000 7889 2000 200 4562 456890 8000 6113 2000 100 5112 60000 7000 3452 23480 4000 9870 18000 1800 9870 26000 2000 3452 35000 5000 6113 5000 500 9887 12556 1234

Regards

Nagarjuna.

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Is this the expected output?

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Thanks @ sunnny Talwar, now it works for me. I need one more favor , Can i sort only top 2 item? i.e not item 1 and item 2 . It should display only 2nd item based on spend. It should work without match function.

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Hahahaha now it works? I did not change a single thing except using the new data. Expression stayed the exact same.

Try this for 2 items

Sum({<[Item ID] = {"=Rank(Sum(Spend)) < 3"}>}Savings)

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I did one mistake, now i understood what it is for the earlier one.Here i need only 4562 item and not 9887 , I have more columns like savings 1, savings 2 and etc for all items , here i want to use bar charts for one item ID , I want to 10 charts for 10 item IDs so wanna sort each item ID i.e Item 1,2,3, and etc is this possible? when i sort item IDs it should display based on spend only.

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

What is a point of pie chart with just one item? Anyways, try this

Sum({<[Item ID] = {"=Rank(Sum(Spend)) < 3 and Rank(Sum(Spend)) > 1"}>}Savings)

I have no idea what you mean here:

I have more columns like savings 1, savings 2 and etc for all items , here i want to use bar charts for one item ID , I want to 10 charts for 10 item IDs so wanna sort each item ID i.e Item 1,2,3, and etc is this possible? when i sort item IDs it should display based on spend only.

Can you share a sample and also please provide the desired output from the same provided

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Thanks @ Sunny talwar it helped me a lot. one more question for you because your answers are damn good. Can i transpose columns of a table like in Excel.

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

In the script? Look here:

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I want to transpose columns in bar chart  and table, i used many formulas, i think its not possible if i load data based on the above script.

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I sorted top spend item and added vendor and spend amount , so now i have 3 columns i.e item ID, Vendor name and spend amount, now i want to add one more column to the table like this,

(total spend for that particular item for that particular vendor/total item sped). Is it possible?

Regards,

Nagarjuna.

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

You want to create a new field?

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Yes

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

May be left join the total spend

Table:

[Vendor name],

[spend amount]

FROM ....;

Left Join (Table)

[Vendor name],

Sum([spend amount]) as TotalSpend

Resident Table

Group By [item ID], [Vendor name];

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Dear Sunny,

We are not getting data from the table,we have  data  already in Excel sheet .From this excel sheet pick the columns like item ID,Vendor Name,Spend Amount. As of my knowledge your script is not fit for this problem.Now we need one additional column to calculate the value from this formula vendor item spend / item spend. These vendor item spend,item spend values are coming from the pivot in excel ,But in qlik sense we are not able to get these values.

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I got the solution, now i want to remove the  item ID. If i remove the item ID all suppliers were changed.It is calculating as a whole.So it is showing other item suppliers also but, i want only specific item ID suppliers.Can u help me on this?

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

How to get one item ID total spend in all rows  based on ranking (i.e here my top 2nd spend item is 4562) i need to get total spend of item ID 4562 i.e 456,890.

Eg:

Regards

Nagarjuna

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

Dear Nagarjuna Vattikuti

fine the attach file and pay attention to Dimension Limits tab and sort tab

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I am confused now. What all have you accomplished and what all are you looking to get help with? Can you provide a Excel example instead of attaching images?

• ###### Re: How to sort items based on spend and i need figures based on savings in the pie-chart

I got the solution, anyway thanks.