topic Re: Table help in QlikView

Table help

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

hey guys,

Sums are not adding correctly.

This is what the table looks like

This is what the database looks like

For my table,

Today's WIP = sum({$< Date={"$(=FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays)))"}>} WIP)

MTD WIP =

sum({$< Month = {"$(=Month(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"},

Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}>} WIP)

Last 30 Days WIP = SUM({<Date={"$(='>=' & date(today()-30))"}>} WIP)

Last 90 Days WIP = SUM({<Date={"$(='>=' & date(today()-90))"}>} WIP)

For some reason the MTD is adding up everything in WIP and not just current month.

Re: Table help

sunny_talwar — Wed, 19 Jul 2017 23:11:23 GMT

How does your Month field look like? How is it created in the script? Using Month function in the script?

Re: Table help

joshrussin — Thu, 20 Jul 2017 14:20:43 GMT

LOAD

Date_Dash,

Month(Date_Dash) as Month,

Num(Month(Date_Dash)) as MonthNum,

Year(Date_Dash) as Year,

MakeDate(year(Date_Dash)) as vCY,

MakeDate(month(Date_Dash)) as vCM

FROM [lib://Data files/OB_Summary_Data.xlsx]

(ooxml, embedded labels, table is OB_Dash_Data);

I have it for a separate excel file, so I should make a new one for this file and call it like Month2?

Re: Table help

sunny_talwar — Thu, 20 Jul 2017 14:24:51 GMT

I have it for a separate excel file, so I should make a new one for this file and call it like Month2?

Not sure I follow this? a separate excel file for what?

Re: Table help

joshrussin — Thu, 20 Jul 2017 14:36:03 GMT

I have a few separate data sources.

one called status

and another called summary

I have connected only the summary in the script, the others I just have them linked.

but for my connection for summary, I have this

LOAD

Date_Dash,

Month(Date_Dash) as Month,

Num(Month(Date_Dash)) as MonthNum,

Year(Date_Dash) as Year,

MakeDate(year(Date_Dash)) as vCY,

MakeDate(month(Date_Dash)) as vCM

FROM [lib://Data files/OB_Summary_Data.xlsx]

(ooxml, embedded labels, table is OB_Dash_Data);

Re: Table help

sunny_talwar — Thu, 20 Jul 2017 15:07:30 GMT

Is Status and Summary linked to each other in a way that date selections flow through to the Status data?

Re: Table help

joshrussin — Thu, 20 Jul 2017 16:25:22 GMT

I figured it out. I just linked all the data in the scripts rather that individually loading. After that I just made month for that data into Month_RD. It is working now. Thanks!