Date Dimension in to 1 line

[Anonymous]

Hi,

Its might be a very basic thing to do but for some reason, I am not able to do it.

I have date Dimension, which is showing in multiple lines, i want to bring total of expression by date in 1 line, as you can see here.

I will really appreciate if someone can help on this.

Regards,

M Talal

[Anonymous]

GI_PVX:

LOAD HistoryId,

     ItemCode as REF,

     ItemName,

     ItemBarcode,

     Datetimestamp as PVX_TIMESTAMP,

     Date(Datetimestamp) as PVX_DATE,

     Year(Date(Datetimestamp)) As PVX_YEAR,

  Month(Date(Datetimestamp)) As PVX_MONTH,

  Day(Date(Datetimestamp)) As PVX_DAY,

     User,

     From,

     To,

     Quantity,

     Comments,

     SalesOrderNumber,

     FromContainer,

     ToContainer,

     LocationTypeFrom,

     LocationTypeTo,

     ManufacturerItemNo,

     ItemGroupName,

     Attribute1 as SupItemCode,

     Attribute2 as Expiry,

     Attribute3 as PGR_NO,

     Attribute4 as PGR_NAME,

     Attribute5 as SGR_NO,

     Attribute6 as SGR_NAME,

     RegisterReason

FROM

(qvd)

WHERE YEAR(Timestamp(Datetimestamp))=YEAR(TODAY());

[Anonymous]

I have tried

=Date(Floor(PVX_DATE),'DD/MM/YYYY')

as well still the same but when i use this in ListBox it gives the required result.

[sunny_talwar]

Difficult to say... ideally I would expect it to work without any issues...

[Anonymous]

Well I created a new field in script, but now the problem is its showing the result of 1 expression at one time, and the other shows '-'.

for each expression i need to use:

if(Comments='Picking',SUM(Quantity))

with different comments parameter. do you know what can be the problem.Please

[sunny_talwar]

Can you show what you get and explain what you want? May be try this with set analysis

Sum({<Comments= {'Picking'}>} Quantity)

[Anonymous]

What are your other expressions?  Maybe one of your other expressions is forcing multiple rows.

Just for troubleshooting purposes, remove all expressions until you have only the one date dimension and one expression and see if it goes to 1 line per date.

[Peter_Cammaert]

Any hidden columns per chance? They have the annoying habit of duplicating identical (sets of) dimension values without any apparent cause because they are ... hidden.

[Peter_Cammaert]

Do not evaluate an IF outside of the aggregation function, as the Comments field in your expression may result in many values. Evaluating a field with multiple values where a single one is expected will result in a NULL value being returned.

If the Comments field may have a different value for each Quantity row, put the IF inside the Sum(). Or way better: use Sunny's Set Analysis example.

[Anonymous]

THANK YOU SO MUCH SUNNY, REALLY APPRECIATE THAT , IT HAS WORKED VERY WELL.

[Peter_Cammaert]

An example of all cases: