Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

mmortsie1
New 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
MVP
MVP

Re: Percent of Distinct Total in a pivot table

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
New Contributor II

Re: Re: Percent of Distinct Total in a pivot table

Hallo,

Does not work

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

Capture.JPG.jpg

Sample document attached

Not applicable

Re: Percent of Distinct Total in a pivot table

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

mmortsie1
New Contributor II

Re: Re: Percent of Distinct Total in a pivot table

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

Re: Re: Percent of Distinct Total in a pivot table

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
New Contributor II

Re: Re: Percent of Distinct Total in a pivot table

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

Re: Re: Percent of Distinct Total in a pivot table

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

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

mmortsie1
New Contributor II

Re: Re: Percent of Distinct Total in a pivot table

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

Re: Re: Percent of Distinct Total in a pivot table

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;

Community Browser