Correct Syntax for Desired Formula

[SRAMIR]

Hi

I´m trying to get to the correct syntax for a formula that calculates the percentage of VIN according to the days they have been in workshop.

I´m attaching the my base file, where I have a number of VIN that are in workshop, with the amount of days they have been in varying from each other. What I´m trying to do is showcase the percentage in the KPI in my dashboard (attached image) according to specific conditions. From the total of VIN, I want to show number of VIN that have been more than 50 days, the ones that have been more than 8 days but less than 50 days and finally those that have been in less than 8 days.

I know this can be reached by entering formula in the KPI tool, however I just can´t get to the correct syntax for it. Can anyone help?

Thanks

[Sayed_Mannan · Accepted solution]

You can achieve this in Qlik by using COUNT function with the help of SET EXPRESSION, here are the corrected formulas:

1. Total number of VINs:-  Count(TOTAL [VIN])

2. VINs more than 50 days: Count({<[Days in workshop]={">50"}>} [VIN])

3. VINs between 8 and 50 days: Count({<[Days in workshop]={">8"}>} [VIN]) - Count({<[Days in workshop]={">50"}>} [VIN])

4. VINs less than 8 days: Count({<[Days in workshop]={"<8"}>} [VIN])

To calculate each percentage, divide each count by the total count of VINs and multiply by 100. For example:

Percentage of VINs more than 50 days: (Count({<[Days in workshop]={">50"}>} [VIN]) / Count(TOTAL [VIN])) * 100

Please replace the formulas in your KPI's  with these corrected ones. Let me know if you need further assistance. Marks this as a solution if it help to solve your query

[MatheusC]

try this expression

Count({<[VIN] = {"=sum([Days in workshop])>50"}>}distinct [VIN])


- Matheus