Percent of Distinct Total in a pivot table

[mmortsie1]

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

[tresesco]

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]

Hallo,

Does not work

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

Sample document attached

[#4]

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

[mmortsie1]

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

[#6]

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]

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

[#8]

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

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

[mmortsie1]

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 

[#10]

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;