Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
kmstephenson
Creator
Creator

Using TOTAL in trending?

Hi All,

I use the following expression to calculate % for any given EndDt selected (always one selected value):

Expression:

COUNT(DISTINCT Key)/COUNT(TOTAL DISTINCT Key)


If I use this for trending in a straight table (i.e. mini chart sparkline) it seems to give me the correct sparkline pattern but not the correct values - I believe this is because how total works. Can someone help explain how this is working?

COUNT({<EndDt>} DISTINCT Key)/COUNT(TOTAL {<EndDt>}DISTINCT Key)

3 Replies
Anil_Babu_Samineni

The above expression works as Count the Unique number of Key and there TOTAL validity. So, Example

Key

1

2

3

4

1

2

Count(DISTINCT Key) -- 4 Where Count(TOTAL DISTINCT Key) also 4 until unless DISTINCT not presence. 4/4 = 1

Count(TOTAL Key) always counts all even unique included like 6

Same as below one, But IT ignores the irrespective of selection for EndDt ??

Does this Helps?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable

Hi,

The Total is ignoring your dimension and in this example doing a sum and a count over all values in a table.

Dim Value      sum(Value)      sum(Total value)      count(Distinct Value)      count(Total Value)

1     100          100                    600                         1                                   3

2     200          200                    600                         1                                   3

3     300          300                    600                         1                                   3

A TOTAL{<Dim>} is aggregating the values for the Dimension 'Dim':

Dim  Value      sum(Value)      sum(Total{<Dim>} value)          

1      100          100                    100                                  

2      500          500                    700                                 

2      200          200                    700                                 

3      300          300                    300                                 

kmstephenson
Creator
Creator
Author

I misspoke above - the sparkline patterns are off but the calculations for each EndDt are correct.  Here is some example data - the key is a concatenation of an ID and end date.

Key                 EndDt                 Numerator

A_201312       2013-12-31        1

A_201403       2014-03-31        1

A_201406       2014-06-30        1

A_201409       2014-09-30        1

A_201412       2014-12-31        1

A_201503       2015-03-31        1

A_201506       2015-06-30        1

A_201509       2015-09-30        1

A_201512       2015-12-31        1

A_201603       2016-03-31        1

A_201606       2016-06-30        1

A_201609       2016-09-30        1

A_201612       2016-12-31        1

A_201703       2017-03-31        1

A_201706       2014-06-30        1

B_201509       2015-09-30        0

B_201512       2015-12-31        1

B_201603       2016-03-31        1

B_201606       2016-06-30        1

B_201609       2016-09-30        1

B_201612       2016-12-31        1

B_201703       2017-03-31        1

B_201706       2017-06-30        1

The sparkline that I'm seeing is as follows:

This is the trendline shape that I should be seeing:

COUNT({<EndDt>}DISTINCT Key)/COUNT(TOTAL{<EndDt>} DISTINCT Key)

Anyone have any idea why this is going on? I validated that the expression is working by computing the above expression with hardcoded EndDts in the set analysis - I get the expected values but the plot doesn't show this in the sparkline of the straight table.

Thanks!

Tina