topic Re: Hide column in pivot table based on calculation of the column itself in App Development

Hide column in pivot table based on calculation of the column itself

Hakim-A — Wed, 01 Jul 2020 13:25:54 GMT

Hello all,

I built a pivot table showing yearly sales for different dimensions + objectives of sales.

The table in QS looks like this

(by clicking on the + button you can drill down to the 5 other dimensions)

Users of my report don't want to see the objectives of sales for past years in this table (but we still need them in the report for other purposes) so I was able to hide the calculation for 2019, 2018 etc.. BUT now they ask me to remove columns "objective 1" and "objective 2" for 2019 and before - they just need these columns for the current year..

I've tried some options in QS dealing with hiding column if and using aggregates to calculate the sum of sales over a year and pass it as a condition to show or hide the column but no success.

Would you have an idea how to achieve this ?

In advance thanks for your help.

Re: Hide column in pivot table based on calculation of the column itself

NitinK7 — Wed, 01 Jul 2020 13:50:25 GMT

hi,

which condition you are trying?

Re: Hide column in pivot table based on calculation of the column itself

Hakim-A — Wed, 01 Jul 2020 13:54:30 GMT

I put that in "Show column if"

=if(
sum(aggr(
if(year(max(date_sales))=year(today()),
Sum({<code_objectiv={'SIGN'},[date_sales_daf.autoCalendar.InYTD] = {'1'}>}sales),
0),
[date_sales_daf.autoCalendar.Year]))
>=1,1,0)

Re: Hide column in pivot table based on calculation of the column itself

NitinK7 — Wed, 01 Jul 2020 14:03:33 GMT

Hi,

please try like

if(year=year(today()),1,0)

may be it is work

Re: Hide column in pivot table based on calculation of the column itself

Hakim-A — Wed, 01 Jul 2020 14:19:36 GMT

Doesn't work it is more complex than that I guess

Re: Hide column in pivot table based on calculation of the column itself

NitinK7 — Wed, 01 Jul 2020 14:28:04 GMT

can you attached your sample qvf here?