topic Re: Ad-hoc reporting, set analysis in QlikView

Ad-hoc reporting, set analysis

aisolomatin — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Ad-hoc reporting, set analysis

ahaahaaha — Wed, 17 May 2017 11:36:05 GMT

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

Re: Ad-hoc reporting, set analysis

Anil_Babu_Samineni — Wed, 17 May 2017 11:42:10 GMT

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

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 13:20:07 GMT

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).

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 13:22:22 GMT

Sorry, but I don't get your suggestion.

Can you please explain.

Thank you!

Re: Ad-hoc reporting, set analysis

Anil_Babu_Samineni — Wed, 17 May 2017 14:01:57 GMT

May be this?

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

Re: Ad-hoc reporting, set analysis

vishsaggi — Wed, 17 May 2017 14:11:51 GMT

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

];

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 14:13:30 GMT

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.

Re: Ad-hoc reporting, set analysis

vishsaggi — Wed, 17 May 2017 14:16:24 GMT

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?

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 14:18:30 GMT

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).

Re: Ad-hoc reporting, set analysis

Anil_Babu_Samineni — Wed, 17 May 2017 14:19:50 GMT

Is it, Can you attach updated QVW, Please

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 14:23:32 GMT

It will be Ad-hoc reporting tool: 1 table. All users will be create they own reports (they can add to Dimensions all necessary fields: Branch, Subbranch, DateReport and other fields). Do you get it?

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 14:26:37 GMT

Check it out.

Re: Ad-hoc reporting, set analysis

vishsaggi — Wed, 17 May 2017 14:28:08 GMT

Ok so why do you even need that column then? Sum(Aggr(Sum()) I mean, you are already getting the values for each datereport using Sum(value) right? Correct me, sorry quite not getting what exactly your are looking for, can you explain a little more with some expected output you want to see.

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 14:37:31 GMT

Yes, in first table expression works good. But, if user decide remove Dimension [DateReport] from table, values become incorrect, like in my example (2nd table).

In final solution (output) user will be decide which Dimensions should be in table.

Re: Ad-hoc reporting, set analysis

Anil_Babu_Samineni — Wed, 17 May 2017 14:55:05 GMT

I am not sure, Whether which values you are expecting while Zero

Like this, May be?

Or this?

Re: Ad-hoc reporting, set analysis

vishsaggi — Wed, 17 May 2017 15:07:24 GMT

Check this?

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 15:18:09 GMT

Yes, work. But you use different expressions in two tables.

Is it possible to make one expression for 2 tables? I mean Expressions with same sintax.

Because in my output solution (Ad-hoc) i will get only one table, and user will be decide which Dimensions should be include to it.

Re: Ad-hoc reporting, set analysis

aisolomatin — Wed, 17 May 2017 15:26:26 GMT

I need one UNIVERSAL expression, which will give me result in first table like this : Sum(Value) for each row and Sum(Value) with DateReport=Max(DateReport) for Total:

and 2nd table like this: Sum(Value) with DateReport=Max(DateReport).

But it shoul be 1 Expression (same syntax).

Re: Ad-hoc reporting, set analysis

vishsaggi — Wed, 17 May 2017 15:30:23 GMT

This ?