Ad-hoc reporting, set analysis

[aisolomatin]

Hi, there!

Need your help with issue: I need to make expression, that will calc a Sum(Value) for max DateReport.

But if DateReport has added to table as Dimension, then I need to get Sum(Value) for current DateReport.

Here example. I try to use expression

=If(Dimensionality()=0,

  Sum({<DateReport={"$(=Max(Date(DateReport)))"}>}Value),

  Sum(Value)).

But problem is that it works fine for first table, but doesn't work for second.

Second table should look like this:

I need to use it in Ad-hoc reporting, so I have one table with all Dimensions (users choose Dimensions for table by themselves), and I need one expression.

[ahaahaaha]

Hi,

If I understood you correctly. May be as at attached file in "Final" table. I inserted a [DateReport] dimension into the table, and then hide it on the tab "Presentation".

Regards,

Andrey

[Anil_Babu_Samineni]

`Seems, This report shows to me as Max Date of Data point. Can you check the same

[aisolomatin]

Andrey, Hi! Thank you for reply.

I guess it's not the thing that I am searching for.

The goal of the Expression that I need is to show Sum(Value) for Max [DateReport], if [DateReport] doesn't addicted to the table (2nd table).

If Dimension [DateReport] has addicted to table, then I need show Sum(Value) for each date (1st table in Example).

[aisolomatin]

Sorry, but I don't get your suggestion.

Can you please explain.

Thank you!

[Anil_Babu_Samineni]

May be this?

Sum(Aggr(Sum({<DateReport = {'$(=Date(Max(DateReport)))'}>}Value), SubBranch, Branch))

[vishsaggi]

Check this if this is what you are looking?

LOAD *,

     Date(Date#(DateReport, 'DD.MM.YYYY'), 'MM/DD/YYYY') AS NewDate;

LOAD *

      INLINE [

    Branch, SubBranch, DateReport, Value

    Branch1, Sub1, 31.01.2017, 808

    Branch1, Sub1, 28.02.2017, 864

    Branch1, Sub1, 31.03.2017, 944

    Branch1, Sub2, 31.01.2017, 857

    Branch1, Sub2, 28.02.2017, 847

    Branch1, Sub2, 31.03.2017, 955

    Branch1, Sub3, 31.01.2017, 953

    Branch1, Sub3, 28.02.2017, 841

    Branch1, Sub3, 31.03.2017, 889

];

[aisolomatin]

Anil,

works pretty nice, But only for 2nd table.

If I use this Expression in First table (with [DateReport] Dimension), it show values only for Max DateReport, but I need values for each DateReport.

[vishsaggi]

Quite did not get you. IF you want all the values for date report you are already getting the column beside sum(Aggr()) column. What is your actual requirement output?

[aisolomatin]

Vishwarath, thanks for reply!

Works good for second table, but doesn't fit for first table (shows values only for Max(DateReport), but I need values for each row).