Straight Table Query With Expression

Ankhi — Sat, 16 Nov 2024 00:18:28 GMT

Hi ,

I have a table in my data model(fact) which is a union/concatenation of data from 2 qvds (Acts, TRC) . There is a column in the table say Status which identifies whether the data is Acts or Trc. E.g. Status = TRC/ACTS.

There are around 40 other columns. Amongst which there are 3 columns called Cost ,Rate and Hour from both tables which can be used as measures and the rest are all dimensions.

Now as a requirement, I need to show the data from Acts with filters like ColA<> 'x' and ColB<=selected date from date filter but with NO sum/aggregation for cost , rate and hour. (So I was using a dummy expression in my straight table)

But for Trc, I need to do a Sum(Cost),Sum(Hours), Sum(Rate) along with ColA<> 'x' and ColB<=selected date from date ilter.

Is it possible to show Cost/Hour/Rate for Acts without sum and Cost/Hour/Rate with sum/aggregate for TRC in this way in the straight table?

Please help.

Thanks in advance

Regards

Ankhi

Re: Straight Table Query With Expression

Kushal_Chawda — Thu, 16 Jul 2020 23:05:05 GMT

try with only functions. But make sure that you have unique values of Cost, Hours for combination of dimensions you are using in the chart otherwise it will not work.

Create variable on front end for max Date which is nothing but selected Date

vMaxDate = date(max(DateField)) // This is the Date field on which you are going to make selection

Then you can below expression

only({<ColA-={'X'},ColB={"<=$(vMaxDate)"}>}Cost)

Similarly you can create the same expression for Hours & Rate.

topic Re: Straight Table Query With Expression in QlikView

Straight Table Query With Expression

Re: Straight Table Query With Expression