topic Re: Weekday wise Production in Expression in QlikView

Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 07:48:57 GMT

Dears,

I have an requirement to take the weekly sales by day. Only the current week. Currently it is adding all the production data and sum it up by day.

I need to know only the week day trend for the current week wherein the week in need to have filters for the user to select.

For e.g.

Week1, Week 2, Week 3, Week 4

Lets say the Production for the week 1 is 10,000, week 2 is 20,000, week3 is 10,000 & week 4 is 10,000. There is a field we pull from the DB as day and we try to sum it up.

As per the attachment Qlik is summing up all the premium and present it day wise. I need to filter records only based on the current week by selecting week1, week 2, week 3 & week 4.

The Expression Field is SUM(T_NETPREM)

The Dimension Field is T_WEEKDAY

Re: Weekday wise Production in Expression

shraddha_g — Thu, 23 Nov 2017 08:00:20 GMT

Share some sample data and expected output

Re: Weekday wise Production in Expression

Anil_Babu_Samineni — Thu, 23 Nov 2017 08:01:16 GMT

In fact, Qlik has power with automate associate engine. So, I would think much efficient to show title indication

Sum({<T_WEEKDAY = {$(=Max(T_WEEKDAY))}>} T_NETPREM)

Re: Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 08:09:43 GMT

When I execute the command it shows NO DATA TO DISPLAY.

The column name is T_Weekday I changed it but still data is showing NO DATA TO DISPLAY

Sum({<T_Weekday = {$(=Max(T_Weekday))}>} T_NETPREM)

Re: Weekday wise Production in Expression

shraddha_g — Thu, 23 Nov 2017 08:12:52 GMT

Try this:

Sum({<T_Weekday = {"$(=Max(T_Weekday))"}>} T_NETPREM)

Else Share the format of data available in T_Weekday Column

Re: Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 08:16:08 GMT

Still no data to display

T_Weekday data in the Table

Sun

Mon

Tue

Sat

Wed

Re: Weekday wise Production in Expression

shraddha_g — Thu, 23 Nov 2017 08:30:04 GMT

How are you defining week1 , Week2 etc.?

Re: Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 08:31:37 GMT

Actually I had not defined week1, 2.

This also needs to be done in case the user wants to compare the weeks to see the growth of the production.

Ashok

Re: Weekday wise Production in Expression

shraddha_g — Thu, 23 Nov 2017 08:43:33 GMT

Try

Sum({<T_Weekday = {"$(=Maxstring(T_Weekday))"}>} T_NETPREM)

Re: Weekday wise Production in Expression

Anil_Babu_Samineni — Thu, 23 Nov 2017 08:57:26 GMT

Try to change, Your week field in script like

Week(DateField) as Week

And then use my expression.

Re: Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 09:04:48 GMT

I have a field as T_APPR_DT and its the actual date.

Shall we use this column to get the weekday

Ashok

Re: Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 09:06:18 GMT

Its not working. It filters the max production date and not showing the current week trend.

Ashok

Re: Weekday wise Production in Expression

Anil_Babu_Samineni — Thu, 23 Nov 2017 09:06:23 GMT

Off course, Using WeekDay(T_APPR_DT) as WeekDay can convert into WeekDays. But to get recent Week you need to convert as Week(T_APPR_DT) as Week

Re: Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 09:12:00 GMT

This line i added in the data extraction Load script.

WeekDay(T_APPR_DT) as WeekDay

Do I need to use the other script Week(T_APPR_DT) as Week in the Expression column. As when I add this in the load script the field is not coming to add it.

Re: Weekday wise Production in Expression

Anil_Babu_Samineni — Thu, 23 Nov 2017 09:14:47 GMT

Yes,

Load T_APPR_DT, //Assume, This is Date Field

Week(T_APPR_DT) as Week,

WeekDay(T_APPR_DT) as WeekDay,

T_NETPREM

From Source;

And then, In front end use your Dimension needed as business for show case

Expression is

Sum({<T_WEEKDAY = {$(Max(T_WEEKDAY))}>} T_NETPREM)

Re: Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 09:34:58 GMT

The script should be like this right as per your statement

Sum({<WeekDay = {$(Max(WeekDay))}>} T_NETPREM)

Re: Weekday wise Production in Expression

Anil_Babu_Samineni — Thu, 23 Nov 2017 09:37:59 GMT

I think Weekday should change into Week

Sum({<Week = {$(=Max(Week))}>} T_NETPREM)

Re: Weekday wise Production in Expression

pashok75 — Thu, 23 Nov 2017 09:42:08 GMT

This statement sums all production. Lets say I have data from 1st November till 23rd November it is summing all the production values and present it week day wise.

My requirement is to only show the current week data alone. In this case the 3rd week.

Ashok

Re: Weekday wise Production in Expression

Anil_Babu_Samineni — Thu, 23 Nov 2017 09:45:34 GMT

From here if we use Week() as Week it converts into 1,2,3. and then i am using Max(Week) that means 3 only, i believe

Re: Weekday wise Production in Expression

shraddha_g — Thu, 23 Nov 2017 09:46:01 GMT

Why don't you try out with WeekStart date then.

In Data model Create,

WeekStart(DateField) as WeekStart

And in Expression, you can use

Sum({<WeekStart = {"$(=Max(WeekStart))"}>} T_NETPREM)