Problems with if statement and ValueList in a Pivot table

Anonymous — Fri, 26 Jan 2018 08:57:24 GMT

Hi,

I am trying to calculate Year over Year values for the revenue of every Quarter in a Pivot table.

I would ideeally like to have my table look like this:

So for every Quarter I can show the revenue for each quarter and then next to it the YoY revenue

(meaning in this case: Revenue FY17-Q1 / Revenue FY16-Q1)

As dimension I use FY_Quarter and a ValueList ('Quarter','YoY')

As expression I try to use

if(ValueList('Quarter','YoY') = 'Quarter',

SUM({<FY_Quarter={'FY17-*'}>}[Revenue 1]),

if(ValueList('Quarter','YoY') = 'YoY',

(SUM({<FY_Quarter={'FY17-*'}>}[Revenue 1])

SUM({<FY_Quarter={'FY16-*'}>}[Revenue 1]))

))

The part to show values for every Quarter seems to be working just fine, but I am really struggling on the YoY part.

Attached is a sample QVW where I tried to replicate my case with sample data.

Is anyone out there who could give me a hint on how that could work?

Thanks

topic Problems with if statement and ValueList in a Pivot table in QlikView