Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mmortsie1
Contributor II
Contributor II

Percent of Distinct Total in a pivot table

Hallo,

If some could help me i need the Distinct total of VOC per Month.

Current Expression

=count(VOC) / count(total <VOC> month(TARGET_DATE)) as Percent

The correct percentage for the first record should be 35,4 which is 495/1325

Capture.JPG.jpg

9 Replies
tresesco
MVP
MVP

But nowhere you used 'DISTINCT' keyword!     Try like:

count(Distinct VOC) / count( Distinct total <VOC> month(TARGET_DATE))

If, this doesn't help, try to share your sample app.

mmortsie1
Contributor II
Contributor II
Author

Hallo,

Does not work

= count(Distinct VOC) / count(Distinct total <VOC> month(TARGET_DATE))

Capture.JPG.jpg

Sample document attached

Not applicable

Where do you get the 1,325 that you refer to in your 1st post?

mmortsie1
Contributor II
Contributor II
Author

Might be that the values have changed now due to the fact that i removed loads of Data to get the file size down.

but the 1325 was Distinct count of VOC for the month of MRZ

Not applicable

The problem is in the <> brackets. The entry in the field there should be the field you are totaling by which in this case should be Month (Not VOC)

count(Distinct VOC) / count(Distinct total <Month> month(TARGET_DATE))


  • I did note from your attached example though that you don't actually have a Month field
  • Since you can't use month(TARGET_DATE) in the <> brackets, I advise you create a Month field in the script instead and then use this
mmortsie1
Contributor II
Contributor II
Author

How can that work a Count Distince over Month will give me in this example only 10 since there are on 10 months in the Data.

What i need is 469 which is a count(VOC) for the Feature 1YSZD for Month MRZ /  by the Distinch Total off all VOC for the month MRZ

Not applicable

Ah sorry, I see my mistake, damn copy paste..

count(Distinct VOC) / count(Distinct total <Month> VOC)

mmortsie1
Contributor II
Contributor II
Author

That works the only problem now is that in the Pivot i dont have the months as a name anymore i have it as a numeric number 

Capture.JPG.jpg

Not applicable

The best idea is then to use a mapping load in the script. Use something like

MAP1:

Mapping LOAD * INLINE [

    Month, MonthName

    1, Jan

    2, Feb

    3, Mar

    4, Apr

    5, May

    6, Jun

    7, Jul

    8, Aug

    9, Sep

    10, Oct

    11, Nov

    12, Dec

];

map Month using MAP1;