Please help with expression

[priya945]

Hi All,

How to get the row-wise total in above table

Ex: for first row 12:00 - 2:59 am , total row value 188(Weekday)+450(weekend) = 638.

thanks in advance

[priya945]

Here is my data and requirement is to create a bar chart with value(Weekday + Weekend ).

Time of Day Weekday Weekend
12:00-2:59 a.m 188 450
3:00-5:59 a.m 142 211
6:00-8:59 a.m 346 63
9:00-11:59 a.m 281 96
12:00-2:59 p.m 382 98
3:00-5:59 p.m 601 159
6:00-8:59 p.m 694 665
9:00-11:59 p.m 470 592

I have achieved the requirement in script using below and in the frontend I have used
Dimension as [Time of Day],
Expression as Sum(New)

Exercise:
LOAD [Time of Day],
Weekday,
Weekday as New
FROM [....\Book1.xlsx]

Concatenate

LOAD [Time of Day],
Weekend as New,
Weekend
FROM [....\Book1.xlsx]

But using above approach I have to load the same table several times based on number of fields to concatenate into one(New) field. so suggest any better approach for below data

Time of Day Weekday Weekend WeekMid
12:00-2:59 a.m 188 450 12
3:00-5:59 a.m 142 211 34
6:00-8:59 a.m 346 63 56
9:00-11:59 a.m 281 96 65
12:00-2:59 p.m 382 98 34
3:00-5:59 p.m 601 159 76
6:00-8:59 p.m 694 665 23
9:00-11:59 p.m 470 592 87

[jensmunnichs]

If all of your data is in the same Excel file, you can just load the fields at the same time. Not really sure why you need the 'New' field.

LOAD [Time of Day], Weekday, Weekend //, WeekMid, etc.
FROM [....\Book1.xlsx];

Bar chart:
Dimension: [Time of Day]
Expression: Sum(Weekday) + Sum(Weekend) 

[priya945]

Thanks for the suggestion that using Sum(Weekday) + Sum(Weekend) in bar chart could solve the issue. But what if I want to achieve the same in the script i.e by concatenate all the values into one filed and use that field in the front end as Sum(newfield)

how to achieve below in script

Newfield = Weekday + Weekend+Weekmid

[jensmunnichs]

Well, if you only need the sum of those 2 and don't care about the Weekday/Weekend distinction, you could simply do the calculation in script:

Temp:
LOAD [Time of Day], Weekday, Weekend //, WeekMid, etc.
FROM [....\Book1.xlsx];

Final:
LOAD [Time of Day], Weekday + Weekend as Week
Resident Temp;

Drop table Temp;

Then you can simply do Sum(Week) with [Time of Day] as dimension to create your bar chart.

If you DO want to keep the distinction, you could use the crosstable prefix:

Crosstable(WeekType, Value, 1) LOAD [Time of Day], Weekday, Weekend //, WeekMid, etc.
FROM [....\Book1.xlsx];

This will create a field called 'WeekType' with values like Weekday, Weekend (WeekMid, whatever else you add), and a field called 'Value' with all the values under Weekday and Weekend. The 1 indicates the amount of fields from the left that should stay as they are, in this case only [Time of Day].

Your bar chart will then have Sum(Value) as expression and [Time of Day] as dimension, and you will still be able to use set analysis to create the distinction between Weekday and Weekend. So in your original post the Weekday column would use Sum({<WeekType = {"Weekday"}>} Value) as its expression.

Hope this helps, if you have any more questions let me know.

[priya945]

Thanks a lot for your time.
Crosstable could be possible solution but just want to enhance my knowledge, what point here made you to think of crosstable .
Please suggest

[jensmunnichs]

The crosstable prefix (just realised I used the dutch link in my last post) allows you to restructure your data, usually turning a wide table into a long (vertical) table. The reason why it works here is because it places all the values into a single field, as you wanted, while still allowing you to make a distinction between the different types of value.

As you use Qlikview more you'll realise there are times that this prefix can be very useful. Take a look at this blog, it explains things a lot better than I ever could. If you want to learn more about Qlikview development, I definitely recommend reading some more blogs by Hic as they're all incredibly useful.

[priya945]

Once again thanks a lot for your time