topic Re: How to show the row numbers in text object in QlikView

How to show the row numbers in text object

rkpatelqlikview — Tue, 17 Jul 2018 02:07:24 GMT

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.

Re: How to show the row numbers in text object

petter — Tue, 17 Jul 2018 04:39:09 GMT

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

Re: How to show the row numbers in text object

rkpatelqlikview — Tue, 17 Jul 2018 05:58:38 GMT

Thanks for your response Petter,

Sure. Will provide the data and QVW.

Thanks

Re: How to show the row numbers in text object

rkpatelqlikview — Tue, 17 Jul 2018 08:48:16 GMT

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

Re: How to show the row numbers in text object

sunny_talwar — Tue, 17 Jul 2018 11:57:59 GMT

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%?

Re: How to show the row numbers in text object

rkpatelqlikview — Tue, 17 Jul 2018 16:02:02 GMT

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.

Re: How to show the row numbers in text object

sunny_talwar — Tue, 17 Jul 2018 16:06:55 GMT

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

Re: How to show the row numbers in text object

rkpatelqlikview — Tue, 17 Jul 2018 17:14:11 GMT

Thanks Sunny. It's working fine.

Great Help.