Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ravikumar_iyana
Contributor

How to get Score in Text Object overall month wise Score

Hi,

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.

DateStore NameABCSalesScore
01-01-2018ABC25105403.4568
02-01-2018DEF2910213.4863
03-01-2018LMN625651235.896
04-01-2018PQR20532845.5689
01-02-2018ABC5912266.852
02-02-2018DEF351685964.852
03-02-2018LMN5915292.6541
04-02-2018PQR523103.2598
01-03-2018ABC61408523180.258
02-03-2018LMN52467517377.264
03-03-2018DEF4952251264.258
04-03-2018PQR22370.451

More Information Please refer below Excel.

Thanks,

Ravi.

13 Replies
vishsaggi
Esteemed Contributor III

Re: How to get Score in Text Object overall month wise Score

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:

SampleFile:

LOAD

     Month(Floor(Num#(Date#(Date, 'M/DD/YYYY')))) AS MonthName,

     Date AS StoreDate,

     [STORE Name] AS StoreName,

     A,

     B,

     C,

     sales,

     score

FROM

[..\SampleData1.xls]

(biff, embedded labels, table is Sheet1$);

LEFT JOIN(SampleFile)

LOAD *,

     IF(ScoreValue <= 20, 'Grp1',

       IF(ScoreValue > 20 AND ScoreValue <=50, 'Grp2',

          IF(ScoreValue > 50, 'Grp3'))) AS GroupFlg;

LOAD StoreDate,

     sales,

     (Sum(A+B+C)/sales)*1000 AS ScoreValue

Resident SampleFile

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')

Capture.PNG

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.

ravikumar_iyana
Contributor

Re: How to get Score in Text Object overall month wise Score

Hi Vishwarath,

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.

vishsaggi
Esteemed Contributor III

Re: How to get Score in Text Object overall month wise Score

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:

LEFT JOIN(SampleFile)

LOAD *,

     IF(Safetyscore <= 20, 'Green',

       IF(Safetyscore > 20 AND Safetyscore <=50, 'Yellow',

          IF(Safetyscore > 50, 'Red'))) AS GroupFlg;

LOAD //StoreDate,

[Vehicle Number],

//TravelledDate,

//[OVSD Count],

//[HA Count],

//[HB Count],

//[CD Count],

// sales,

(RangeSum(sum([OVSD Count]), Sum([HA Count]), Sum([HB Count]), Sum([CD Count]))/Sum([Kms Travelled]))) * 1000 as Safetyscore

Resident SampleFile

Group By  [Vehicle Number];

ravikumar_iyana
Contributor

Re: How to get Score in Text Object overall month wise Score

Hi Viswarath,

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

vishsaggi
Esteemed Contributor III

Re: How to get Score in Text Object overall month wise Score

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?

ravikumar_iyana
Contributor

Re: How to get Score in Text Object overall month wise Score

Thanks Vishwarath,

Sure,

> 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.

vishsaggi
Esteemed Contributor III

Re: How to get Score in Text Object overall month wise Score

Will look into it tomorrow.

ravikumar_iyana
Contributor

Re: How to get Score in Text Object overall month wise Score

Hi Vishwarath,

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?

Thanks,

Ravi.

vishsaggi
Esteemed Contributor III

Re: How to get Score in Text Object overall month wise Score

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?