topic Dynamic Set Analysis or If clause possibility in App Development

Dynamic Set Analysis or If clause possibility

sangeeth8787 — Thu, 21 Feb 2019 11:51:33 GMT

Just want to check how to re-write the following code in workable way

=sum({<DimensionTable1.ColumnA= Facttable.ColumnA, DimensonTable1.Column = {'2019'}>}Sales)

In Pivot chart, Instead of DimensionTable1.ColumnA, I need to use Facttable.ColumnA due to user requirement.

Thank you

Re: Dynamic Set Analysis or If clause possibility

sunny_talwar — Thu, 21 Feb 2019 12:07:31 GMT

May be this

=Sum({<DimensonTable1.Column = {'2019'}>}
   If(DimensionTable1.ColumnA = Facttable.ColumnA, Sales)
)

Re: Dynamic Set Analysis or If clause possibility

sangeeth8787 — Thu, 21 Feb 2019 13:52:59 GMT

Hi,

I am glad for your message, unfortunately it didn't worked for me,

If I use calculated dimension

If(isnull(Facttable.ColumnA),DimensionTable.Column,Facttable.ColumnA)

Expression

sum({<DimensionTable2.ColumnB = {'2019'}>})

Then it is giving me correct answer, but I am not allowed to use Calculated Dimension @ my work place.

Can you we convert the above calculated dimension to Expression where I need to use Facttable.ColumnA as dimension column in Used Dimensions Section

Re: Dynamic Set Analysis or If clause possibility

sunny_talwar — Thu, 21 Feb 2019 14:03:28 GMT

Might be helpful if you are able to share a sample with the output you are expecting to see

Re: Dynamic Set Analysis or If clause possibility

sangeeth8787 — Tue, 26 Feb 2019 09:29:46 GMT

Hi,

I am glad for your message, Please find the attached pic for example scenario.

Re: Dynamic Set Analysis or If clause possibility

sunny_talwar — Tue, 26 Feb 2019 19:48:47 GMT

at least share the Excel itself instead of sharing an image of an Excel file.

Re: Dynamic Set Analysis or If clause possibility

sangeeth8787 — Wed, 27 Feb 2019 07:22:04 GMT

Hi,

Please find the excel attachment.

Many Thanks

Re: Dynamic Set Analysis or If clause possibility

sunny_talwar — Wed, 27 Feb 2019 12:45:31 GMT

May be try this

QUALIFY *;
UNQUALIFY ProductKey;

FactTableA:
LOAD * INLINE [
    ProductKey, Organisation, SalesAmount, ProductCategory
    1, ABC, 200, Bikes
    2, BCA, 500, Accessories
    3, ABB, 800, Tubes
    0, ADD, 300, 
    0, ACC, 200, 
];

FactTableB:
LOAD * INLINE [
    ProductKey, SalesAmount
    1, 100
    2, 1100
    3, 600
];

Dimension:
LOAD * INLINE [
    ProductKey, ProductCategory
    1, Bikes
    2, Accessories
    3, Tubes
    0, -
];

UNQUALIFY *;

Dim:
LOAD * INLINE [
    Dim
    1
    2
];

and then create a chart like this

Dimension

=Pick(Dim, Dimension.ProductCategory, FactTableA.Organisation)

Expressions

Pick(Dim, Sum({<Dimension.ProductCategory -= {'-'}>}FactTableA.SalesAmount), Sum({<Dimension.ProductCategory = {'-'}>}FactTableA.SalesAmount))

and

Pick(Dim, Sum(FactTableB.SalesAmount))

Not sure how much improvement it will give, but give it a shot

Re: Dynamic Set Analysis or If clause possibility

sangeeth8787 — Thu, 28 Feb 2019 14:37:30 GMT

Hi,

I am glad for your message, If I am using the following expression in dimensions tab, the performance turning out be very poor.
=Pick(Dim, Dimension.ProductCategory, FactTableA.Organisation).

I believe it might be working by creating dummy values to dimension table and assign the same keys to dimension and fact tables.

Re: Dynamic Set Analysis or If clause possibility

sangeeth8787 — Thu, 28 Feb 2019 14:40:03 GMT

Hi,

The following attachment might be works in such scenario's for any reporting platform.

Thanks