Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
jworthulf
New Contributor III

How to create a calculated dimension for DOW

I'd like to create a dynamic dimension for each day of the week, to show the average pounds shipped on that day when I select my time period.  

I've created the measure using Avg(aggr(sum(LBs),VENDOR,fiscal_week,DOW)).

Now I'd like to create a dimensions for each day so I can see the average lbs for every day of the week....

I'm thinking I should be able to do this using a valuelist, pick, and match functions but I'm not sure how to set to go about setting this up.

4 Replies
Highlighted
rodolfoag
New Contributor III

Re: How to create a calculated dimension for DOW

John, can you set a example here? When you mean "day", you mean : just the number (1 to 31), the complete date, the day in year.

RS

Highlighted
jworthulf
New Contributor III

Re: How to create a calculated dimension for DOW

Hi Rodolfo,

I'm referring to the day in terms of Mon, Tue, Wed, Thurs.....

Highlighted

Re: How to create a calculated dimension for DOW

When we talk about ValueList() it shows always static that i don't prefer to use. But in your way if you want forcibly try this

Calculated Dimension

ValueList('Mon','Tue','Wed',Thu',Fri','Sat','Sun')


And Expression looks like

If(ValueList('Mon','Tue','Wed',Thu',Fri','Sat','Sun') = 'Mon', Measure for Monday,

If(ValueList('Mon','Tue','Wed',Thu',Fri','Sat','Sun') = 'Tue', Measure for Tuesday,

If(ValueList('Mon','Tue','Wed',Thu',Fri','Sat','Sun') = 'Wed', Measure for Wednesday,

If(ValueList('Mon','Tue','Wed',Thu',Fri','Sat','Sun') = 'Thu', Measure for Thursday,

If(ValueList('Mon','Tue','Wed',Thu',Fri','Sat','Sun') = 'Fri', Measure for Friday,

If(ValueList('Mon','Tue','Wed',Thu',Fri','Sat','Sun') = 'Sat', Measure for Saturday,

If(ValueList('Mon','Tue','Wed',Thu',Fri','Sat','Sun') = 'Sun', Measure for Sunday)))))))


So, My approach is an If we have option called WeekDay(DateField) as WeekDay may produce as needs,


Dimension as WeekDay

Expression as

Pick(Match(WeekDay, 'Mon','Tue','Wed',Thu',Fri','Sat','Sun'), 'Measure for Monday',Measure for Tuesday',Measure for Wednesday',Measure for Thursday',Measure for Friday',Measure for Saturday'Measure for Sunday')


Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
rodolfoag
New Contributor III

Re: How to create a calculated dimension for DOW

Can't you create a field this way?

weekday(date) as DOW

then use DOW as a dimension of your chart or table, this way, no matter which selection, it will calculate the expression for each day of week.

RS