16 Replies Latest reply: Jan 8, 2018 7:05 AM by Sunny Talwar

Hello all,

I have created two tables1, one with four measure whose headers are 2016, 2017, 2018 and sub headers are Final Revenue and Final Margin respectively and second table2 with two measure whose header is Rank and Growth and sub header are Swing and rate.

I have created sub headers Final Revenue and Final Margin into an inline load as H1 and sub header ! rate and Swing also in inline load as H2

Have attached the snapshot below and have attached the qvf of the same.

Now I want to have this measures of table2 with header Rank and Growth in table1 i.e. I kind of want to merge these two tables into one so that I can have five measures with headers 2016, 2017, 2018, Rank and growth and sub headers final revenue and final margin under headers 2016, 2017, 2018 and Swing and rate under header Rank and Growth.

Kindly help.

stalwar1 kindly help on this plss.

Would you be able to share Book1 Excel file so that we can make changes to the script and reload the dashboard?

yeah, Sure.

Try this

If(H1 = 'Final Revenue',

If(Year = 2016, Sum(REV1),

If(Year = 2017, Sum(REV2) + Sum(REV3),

If(Year = 2018, Sum(REV1)+ Sum(REV2)))),

If(H1 = 'final Margin',

If(Year = 2016, Num(Sum(Margin)/(Sum(REV1)), '##.##'),

If(Year = 2017, Num(Sum(Margin)/(Sum(REV2) + Sum(REV3)), '##.##'),

If(Year = 2018, Num(Sum(Margin)/(Sum(REV1) + Sum(REV2)), '##.##')

))),

If(H1 = 'Swing',

If(Year = 'Rank', Sum(REV3) - Sum(REV2),

If(Year = 'Growth', Sum(REV2) - Sum(REV3))),

If(H1 = 'rate',

If(Year = 'Rank', Sum(REV1)-Sum(REV2),

If(Year = 'Growth', Sum(REV1)-Sum(REV2)))))))

My headers 2016, 2017 and 2018 are dynamic thats why i created them as measures so as to use there labels for dynamic labels.

Kindly suggest changes in the same way i have done in the app i shared with you the very first time.

You can still do this using dollar sign expansion... but with your sample, I didn't know how can we make them dynamic... can you share a sample where we can see an year field which is what you use in your expression . All you use was REV1, REV2, REV3... how do we know which year belongs to what

Hi ,

Have attached a qvf with some changes including dynamic header as well as its excel file.

The dynamic headers depends upon a  year filter.

Upon selection of 2018 one column shows the data of 2018 while the column shows the data of previous year, thats why i created measures in order to make labels dynamic.

I want to merge these two tables keeping this dynamic header thing in mind. so kindly help.

Try like attached

how are my headers are changing dynamically as i cant find any expression in label field.

Dimension is driving them

=If(H2 = 1, \$(=Max(Year) - 1),

If(H2 = 2, \$(=Max(Year)), H2))

Hi Sunny,

Actually my year is in format FY17 and FY18.

So I m not able to edit expression of column as per my requirement.

Use MaxString instead of Max in this expression and other places you might need in set analysis....

How can we use variable inside column expression editor.?

Not sure I understand this....

What if I need to subtract 1 from max of Year.

Maxstring is not working in that case.