How to show the row numbers in text object

[rkpatelqlikview]

Dear Qlik,

Can any one please provide the solution on below.

I have an expression in straight table, I want to show Only 'Yes' counts in text object.

This is my expression in straight table.

= IF( Round((sum({<Datums={"$(=Max(Datums))"},Category={'Green'}>} Distance) / Sum({<Datums={"$(=Max(Datums))"},Category={'Green'}+{'Yellow'}+{'Red'}>} Distance))*100,0.01)<80, 'Yes',

IF(Round(Count({<Diff30=,Datums={"$(=Max(Datums))"},Diff7={'Yes'}>} DISTINCT [IVMS Device ID])/

Count(DISTINCT {<Diff30=, Datums={"$(=Max(Datums))"}>}([IVMS Device ID])) *100) <80, 'Yes'))

Thanks in advance.

[petter]

If you could provide more information about what you want to achieve like showing how the table should look like visually along with some sample data then it would be easier for us to understand your problem and help you.

- Thanks

[rkpatelqlikview]

Thanks for your response Petter,

Sure. Will provide the data and QVW.

Thanks

[rkpatelqlikview]

Dear Petter,

Please find the attached QVW for that logic.

Here If   KPI1 and KPI2  are less than 80 then that should show 'Yes'.

I want to see only Yes category in straight table.

IF any one from both the KPI's Less than 80, it should be in 'Yes' category and i want to see that count of Dim2 names  in the Text object.

Please let me know if any clarification. Thanks in adavance

[sunny_talwar]

What is the expected output for the data provided? Are you hoping to see 3 in a text box object because 944083, 943658, and 943882 meet the criteria that one of the two KPIs have percentage below 80%?

[rkpatelqlikview]

Thanks Sunny,

Please find the attached QVW for required output.

Here

1) In straight table i should see the substores which are <80%. IF any KPI is <80% it should be in straight table. 

2) Count of the Substores which are in 'Yes' category should be in Text object.

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this for text box

=Sum(Aggr(

If(Round((sum({<Datums={"$(=Max(Datums))"},Category={'Green'}>} Distance) / Sum({<Datums={"$(=Max(Datums))"},Category={'Green'}+{'Yellow'}+{'Red'}>} Distance))*100,0.01) < 80 or

Round(Count({<Mode=,Datums={"$(=Max(Datums))"},Diff7={'Yes'}>} DISTINCT [IVMS Device ID])/Count(DISTINCT {<Mode=, Datums={"$(=Max(Datums))"}>}([IVMS Device ID])) *100) < 80, 1, 0), StoreID, substores))

And this for the chart

KPI1

=If(Round((sum({<Datums={"$(=Max(Datums))"},Category={'Green'}>} Distance) / Sum({<Datums={"$(=Max(Datums))"},Category={'Green'}+{'Yellow'}+{'Red'}>} Distance))*100,0.01) < 80 or

Round(Count({<Mode=,Datums={"$(=Max(Datums))"},Diff7={'Yes'}>} DISTINCT [IVMS Device ID])/Count(DISTINCT {<Mode=, Datums={"$(=Max(Datums))"}>}([IVMS Device ID])) *100) < 80,

Round((sum({<Datums={"$(=Max(Datums))"},Category={'Green'}>} Distance) / Sum({<Datums={"$(=Max(Datums))"},Category={'Green'}+{'Yellow'}+{'Red'}>} Distance))*100,0.01) &' %')

KPI2

=If(Round((sum({<Datums={"$(=Max(Datums))"},Category={'Green'}>} Distance) / Sum({<Datums={"$(=Max(Datums))"},Category={'Green'}+{'Yellow'}+{'Red'}>} Distance))*100,0.01) < 80 or

Round(Count({<Mode=,Datums={"$(=Max(Datums))"},Diff7={'Yes'}>} DISTINCT [IVMS Device ID])/Count(DISTINCT {<Mode=, Datums={"$(=Max(Datums))"}>}([IVMS Device ID])) *100) < 80,

Round(Count({<Mode=,Datums={"$(=Max(Datums))"},Diff7={'Yes'}>} DISTINCT [IVMS Device ID])/Count(DISTINCT {<Mode=, Datums={"$(=Max(Datums))"}>}([IVMS Device ID])) *100) &'%')

[rkpatelqlikview]

Thanks Sunny. It's working fine.

Great Help.