Dates in Sum Expressions

anamiac — Sat, 16 Nov 2024 02:43:44 GMT

Hello,

I have a table with three fields: [Shipment Due Date], [Actual Shipped Date] and [Shipped Dollar Value]. I'm trying to create a chart with date as a dimension and 8 fields:

The sum of the moneys expected/due to be shipped on that date
The sum of the moneys actually shipped on that date
For that date, the sum of the moneys expected to be shipped between that date and the first of the month.
For that date, the sum of the moneys actually shipped between that date and the first of the month.
The sum of the moneys expected/due to be shipped on that date 1 year ago
The sum of the moneys actually shipped on that date 1 year ago
For that date, the sum of the moneys expected to be shipped between that date and the first of the month 1 year ago.
For that date, the sum of the moneys actually shipped between that date and the first of the month 1 year ago.

I have a master calendar, I'm attempting to use its 'Date' field as my dimension. This is my expression for the first field:

Sum ({$<Date={[Shipment Due Date]}>} [Shipped Dollar Value])

But the field is returning 0 for all dates. Anyone able to see what I'm doing wrong?

Re: Dates in Sum Expressions

Frank_Hartmann — Thu, 15 Aug 2019 16:12:32 GMT

please share qvw or rawdata

Re: Dates in Sum Expressions

anamiac — Thu, 15 Aug 2019 16:30:52 GMT

@Frank_Hartmann wrote:
please share qvw or rawdata

The qvw is 440 MB and I'm not sure if my company policies allow me to share it online. I've attached the data as it appears after the load (the internal table).

Re: Dates in Sum Expressions

anamiac — Wed, 21 Aug 2019 14:47:40 GMT

This is solved, not by expressions, but by modifying my load script. It was originally something like this:

ShipmentActual:
Load [Shipped Date Actual] AS [Actual Ship Date],
[Ship Date] As [Expected Ship Date],
IF($(_SysDBVersionNum)>=13.2,
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] /
[Shipping Price Unit Conversion]),
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] *
[Shipping Price Unit Conversion])
) as [Shipped Dollar Value],
%ShipReqLineKey
From $(_SysPathQVDDirectory)$(_SysQVDPrefix)ShippingRequests.qvd (qvd)
WHERE NOT IsNull([Shipped Picked Quantity]);

Here's what it is now:

ShipmentActual:
Load [Shipped Date Actual] AS [SA Shipment Date],
IF($(_SysDBVersionNum)>=13.2,
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] /
[Shipping Price Unit Conversion]),
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] *
[Shipping Price Unit Conversion])
) as [SA Actual Shipped Dollar Value],
%ShipReqLineKey,
month([Shipped Date Actual]) as [SA Shipment Month],
year([Shipped Date Actual]) as [SA Shipment Year]
From $(_SysPathQVDDirectory)$(_SysQVDPrefix)ShippingRequests.qvd (qvd)
WHERE NOT IsNull([Shipped Picked Quantity]);

Concatenate(ShipmentActual)
Load
[Ship Date] AS [SA Shipment Date],
IF($(_SysDBVersionNum)>=13.2,
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] /
[Shipping Price Unit Conversion]),
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] *
[Shipping Price Unit Conversion])
) as [SA Expected Shipped Dollar Value],
%ShipReqLineKey
From $(_SysPathQVDDirectory)$(_SysQVDPrefix)ShippingRequests.qvd (qvd)
WHERE NOT IsNull([Shipped Picked Quantity]);

Concatenate(ShipmentActual)
Load addyears([Shipped Date Actual],1) AS [SA Shipment Date],
IF($(_SysDBVersionNum)>=13.2,
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] /
[Shipping Price Unit Conversion]),
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] *
[Shipping Price Unit Conversion])
) as [SA PrevYear Actual Shipped Dollar Value],
%ShipReqLineKey
From $(_SysPathQVDDirectory)$(_SysQVDPrefix)ShippingRequests.qvd (qvd)
WHERE NOT IsNull([Shipped Picked Quantity]);

Concatenate(ShipmentActual)
Load
addyears([Ship Date],1) AS [SA Shipment Date],
IF($(_SysDBVersionNum)>=13.2,
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] /
[Shipping Price Unit Conversion]),
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] *
[Shipping Price Unit Conversion])
) as [SA PrevYear Expected Shipped Dollar Value],
%ShipReqLineKey
From $(_SysPathQVDDirectory)$(_SysQVDPrefix)ShippingRequests.qvd (qvd)
WHERE NOT IsNull([Shipped Picked Quantity]);

ShipmentActual2:
load
[SA Shipment Date] as [SA2 Shipment Date],
sum([SA Actual Shipped Dollar Value]) as [SA2 Actual Shipped Dollar Value],
sum([SA Expected Shipped Dollar Value]) as [SA2 Expected Shipped Dollar Value],
sum([SA PrevYear Actual Shipped Dollar Value]) as [SA2 PrevYear Actual Shipped Dollar Value],
sum([SA PrevYear Expected Shipped Dollar Value]) as [SA2 PrevYear Expected Shipped Dollar Value],
%ShipReqLineKey,
Day([SA Shipment Date]) as [SA2 Small Day]
resident ShipmentActual
group by [SA Shipment Date], %ShipReqLineKey;

let vMaxDays = 31;
for vOuterLoop = 0 to vMaxDays
for vInnerLoop = vOuterLoop to vMaxDays
DayMapping:
LOAD
$(vOuterLoop) as [smaller date],
$(vInnerLoop) as [larger date]
AUTOGENERATE(1);
next
next

left join (ShipmentActual2)
load
[smaller date] as [SA2 Small Day],
[larger date] as [SA2 Large Day]
Resident DayMapping;

Concatenate(ShipmentActual)
Load
date([SA2 Shipment Date] - [SA2 Small Day] + [SA2 Large Day]) as [SA Shipment Date],
[SA2 Actual Shipped Dollar Value] as [SA MTD Actual Shipped Dollar Value],
[SA2 Expected Shipped Dollar Value] as [SA MTD Expected Shipped Dollar Value],
[SA2 PrevYear Actual Shipped Dollar Value] as [SA MTD PrevYear Actual Shipped Dollar Value],
[SA2 PrevYear Expected Shipped Dollar Value] as [SA MTD PrevYear Expected Shipped Dollar Value],
%ShipReqLineKey,
month([SA2 Shipment Date]) as [SA Shipment Month],
year([SA2 Shipment Date]) as [SA Shipment Year]
Resident ShipmentActual2
where month([SA2 Shipment Date] - [SA2 Small Day] + [SA2 Large Day]) = Month([SA2 Shipment Date]);

drop table ShipmentActual2;
drop table DayMapping;
let vMaxDays=;
let vOuterLoop=;
let vInnerLoop=;

Re: Dates in Sum Expressions

Brett_Bleess — Wed, 28 Aug 2019 20:47:31 GMT

Joseph, you can actually reduce the app quite a bit by using the File\Reduce Data\Keep Possible Values, but you need to make some selections first to be able to keep enough to still show the issue and data model etc. The other piece you can use is in Settings\Document Properties\Scrambling tab to scramble values in a fields that are confidential etc.

Regards,
Brett

topic Dates in Sum Expressions in QlikView

Dates in Sum Expressions

Re: Dates in Sum Expressions

Re: Dates in Sum Expressions

Re: Dates in Sum Expressions

Re: Dates in Sum Expressions