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: 
kira_whopper
Creator
Creator

Market Share with calculated dimension

Hi there,

I have a calculated dimension for showing the Top n Corporations + the clients Corporation on a line chart and on a table.

The line chart needs only the Corporations and the Table needs a line with Others.

This is the calculated dimension for the Chart:

IF(

Aggr(

Rank(TOTAL

Sum({<YearMonth ={"$(=Date(Max(YearMonth), 'MMM-YY'))"},Year=,Month=>}

  Value)

), Corporation

) <= vUPLIFT

OR Corporation = 'Client Corporation',

Corporation

)

And this is for the table:

IF(

Aggr(

Rank(TOTAL

Sum({<YearMonth ={"$(=Date(Max(YearMonth), 'MMM-YY'))"},Year=,Month=>}

  Value)

), Corporation

) <= vUPLIFT

OR Corporation = 'Client Corporation',

Corporation,

'Others'

)

The only difference between them is the else 'Others' for the table.

Ok, so I need to calculate the Market Share in both the chart and the table.

In the table works fine, but in the line chart, the total does not consider the Corporations out of the Rank in the calculated dimension, unless I set the dimension to consider null values, but then I'll have always a line in the chart for the null values, which is not what I'm trying to achieve

This is the expression for Market Share calculation:

Sum({<YearMonth ={">=$(=Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},Year=,Month=>}

  Value)

/

Sum(TOTAL <YearMonth> {<YearMonth ={">=$(=Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},Year=,Month=, Corporation=>}

  Value)

So, if I use the calculated dimension with others or accept null values, it shows the proper results. But if disconsider null values using the calculated dimension without others, I'm not able to calculate the total of all Corporations for the right Market Share value.

I need a way to calculate that total per month.

Any ideas?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Just use the expression like I suggested, including an outer aggregation and set expression:

Sum({<PeriodMTH ={">=$(=Date(AddMonths(Max(PeriodMTH), -12), 'MMM-YY'))<=$(=Date(Max(PeriodMTH), 'MMM-YY'))"}, Period=>}

  Soma)

/

Sum({<PeriodMTH ={">=$(=Date(AddMonths(Max(PeriodMTH), -12), 'MMM-YY'))<=$(=Date(Max(PeriodMTH), 'MMM-YY'))"}, Period=, Prod=>}

Aggr(NODISTINCT

Sum({<PeriodMTH ={">=$(=Date(AddMonths(Max(PeriodMTH), -12), 'MMM-YY'))<=$(=Date(Max(PeriodMTH), 'MMM-YY'))"}, Period=, Prod=>}

  Soma)

, PeriodMTH))

View solution in original post

5 Replies
marcus_sommer

I'm not sure if it led in the right direction but you could try with 1 as set identifier to include the excluded dimension values. Another thought is to calculate your total within a variable which is then independent to your dimension values (they won't be considered and calculate globally) and using them as divisor.

- Marcus

swuehl
MVP
MVP

Using a variable to calculate your market Value in total context might work, but not if you need to get the TOTAL market value across the YearMonth dimension.

Maybe you can use advanced aggregation to get all dimension values into your calculation:

Sum(<YearMonth ={">=$(=Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},Year=,Month=, Corporation=>}

Aggr(NODISTINCT

Sum({<YearMonth ={">=$(=Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},Year=,Month=, Corporation=>}

  Value)

,YearMonth)

)

for your total market value calculation (divisor in the line chart expression).

If this doesn't help, then please post a small sample QVW to have a more detailed look into your setting.

Regards,

Stefan

kira_whopper
Creator
Creator
Author

The formula you proposed brings the correct value, but shows only one month-year.

Attached is an example.

swuehl
MVP
MVP

Just use the expression like I suggested, including an outer aggregation and set expression:

Sum({<PeriodMTH ={">=$(=Date(AddMonths(Max(PeriodMTH), -12), 'MMM-YY'))<=$(=Date(Max(PeriodMTH), 'MMM-YY'))"}, Period=>}

  Soma)

/

Sum({<PeriodMTH ={">=$(=Date(AddMonths(Max(PeriodMTH), -12), 'MMM-YY'))<=$(=Date(Max(PeriodMTH), 'MMM-YY'))"}, Period=, Prod=>}

Aggr(NODISTINCT

Sum({<PeriodMTH ={">=$(=Date(AddMonths(Max(PeriodMTH), -12), 'MMM-YY'))<=$(=Date(Max(PeriodMTH), 'MMM-YY'))"}, Period=, Prod=>}

  Soma)

, PeriodMTH))

kira_whopper
Creator
Creator
Author

Works like a charm! Thanks a lot!