Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Percent of Total and Sorting in a Pivot Table with a Master Dimension

Hello all,

I've been able to have a measurement calculating percent of total units sold for each year in a pivot table with years across the top (the columns) and customers along the side (the rows) using the formula:

Sum(QNT_SOLD)/Sum(TOTAL <[DATE_SHIP.aCal.Year]> QNT_SOLD)

I also had the customers sorted by QNT_SOLD in 2019 by sorting the customer dimension by the expression:

Sum({<DATE_SHIP.aCal.Year={2019}>} QNT_SOLD)

Later, I changed the columns from the Year dimension to a master dimension called DATE_SHIP.Season which uses a variable and the YEARSTART function to have a dynamic season of sorts, and it works well.  The user can choose from a drop down menu that assigns a value to the variable and thereby allows him/her to choose which month is the start of the season very efficiently.  The table updates nicely when different months are selected.  However, my percent of total column and sort methods described above stopped working.  Tried updating both formulas with DATE_SHIP.Season instead of DATE_SHIP.aCal.Year but no luck.   On the sort I also used an IF statement to make it change to 2018-2019 if the month selected for the season start isn't January.

Any suggestions would be greatly appreciated!

Labels (3)