I need to show the top 3 store ID based on the score over the month.
This is i can able to see in the 'straight table' but i could not able show in the 'text object'.
My score expression is (Sum(A+B+C)/Sales)*1000 , Here I need to categorize in to 3 groups.
1) Score which is <= 20 , I need to show the store name whose score start from "0". and their sales.
2) Score >20 and <=50, Here i need to show top 3 score store names and their score as well
3) Score >50, Here also i need to show top 3 score store names and their score as well.
More Information Please refer below Excel.
based on your excel sheet try something like this? And not sure about your 1st point you mentions saying you want values 0. Could you elaborate on that please like what values you are expecting to see for your 1st requirement. For now i am showing top 3 values for all the 3 conditions. Try below:
Month(Floor(Num#(Date#(Date, 'M/DD/YYYY')))) AS MonthName,
Date AS StoreDate,
[STORE Name] AS StoreName,
(biff, embedded labels, table is Sheet1$);
IF(ScoreValue <= 20, 'Grp1',
IF(ScoreValue > 20 AND ScoreValue <=50, 'Grp2',
IF(ScoreValue > 50, 'Grp3'))) AS GroupFlg;
(Sum(A+B+C)/sales)*1000 AS ScoreValue
Group By StoreDate,A,B,C,sales;
Using straight table added:
Dim: Storename, sales, GroupFlg
Expr: = Num(IF(Aggr(Rank(Sum(ScoreValue)), GroupFlg,StoreName) <=3, ScoreValue), '#,##0.000')
In the image above the below 3 text boxes show top 3 scores for different groups.
let us know if this is not what you are looking for. We can dig through. The big text box in the bottom shows what expr i have used in those 3 textobjects to get top 3.
Thanks for your valuable support.
please find attached QVW file which we are implemented as per your suggestion,
Still I am struggling to show the proper result, please find attachment.
Did the requirement changed? The file i am looking at does not have top 3 scores. Can you explain a little more of why SARDARA has to come in Green flag.
I believe the data your produced in your excel does not work here as you have to sum([Km Traveled]) and sum of HA Count and other fields to be included. Can you share your Safety SND Report.xlsx file so that i can run and change the expressions accordingly.
Mean while you can try like below:
IF(Safetyscore <= 20, 'Green',
IF(Safetyscore > 20 AND Safetyscore <=50, 'Yellow',
IF(Safetyscore > 50, 'Red'))) AS GroupFlg;
(RangeSum(sum([OVSD Count]), Sum([HA Count]), Sum([HB Count]), Sum([CD Count]))/Sum([Kms Travelled]))) * 1000 as Safetyscore
Group By [Vehicle Number];
Thanks for your reply.Tried with above script.
Here i selected carrierCode "916002585"
so that in Green Category the highest kms travelled 4991 "SARDARA SINGH". If i select month name should change the based on the highest kms travelled.
In green we need consider kms travelled. and Yellow & Red we should consider the Highest score.
Please find the attached
OK not sure what is your expected output is. Can you elaborate. So where and what you want to see in your sheet when you select CarrierCode can you tell me the output you want to see?
> First thing I want to show in text object the No. of Green Kms travelled drivers , Yellow and red kms travelled Drivers.
> Along with this information as i discussed, the below information also i should provide in the Text objects.
> Top 5 Green Driver Names based KMS travelled and their kms, Top 5 Yellow drivers based on their score and top 5 Red drivers based in their score.
(If Score is high that Driver is comes in 1st place, Next high score driver name is comes in 2nd place etc)
These score will be aggregate over the month.
Score =(sum([CD Count]+[HA Count]+[OVSD Count]+[HB Count])/sum([Kms Travelled])) *1000
1) Green -> Score<=20
2) Yellow -> Score 20 -50
3) Red -> Score above 50
Please let me know if any other information.
Thanks for your great support!!
I tried a lot for this task, but still its not resolved. Is there any chances from your end?
can you please help me on this?
Sorry Ravi for late response. I was so caught up with my child could not able to look into this. I will look into it today and get back. Can i know what timezone you work in?