topic Re: Please help with expression in QlikView

Please help with expression

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

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

Re: Please help with expression

jensmunnichs — Thu, 29 Nov 2018 13:25:33 GMT

Add another expression:

Column(1) + Column(2)

Note that you might have to adjust the 1 or 2 based on how many measures you have and where they are located, this is just based on the table in the image you posted.

More info on Column function

Some other ways to solve this problem in this thread

Re: Please help with expression

priya945 — Thu, 29 Nov 2018 14:32:04 GMT

Hi,

Thank you for your suggestion.

Could you please help me how to find the same in Bar chart

Re: Please help with expression

jensmunnichs — Thu, 29 Nov 2018 14:40:48 GMT

I'm pretty sure you can do the same thing in a bar chart. Another option would be to label the expressions you use to find Weekday and Weekend, then use those names in the third expression (in this case simply Weekday + Weekend)

Re: Please help with expression

priya945 — Thu, 29 Nov 2018 15:02:13 GMT

Agreed that if I am using 3 expressions I am able to plot bar chart but if the requirement is to show only (Weekday + Weekend) expression as below then this is change

Re: Please help with expression

jensmunnichs — Fri, 30 Nov 2018 06:55:57 GMT

I think the only way to hide expressions in a bar chart is by ticking the 'invisible' checkbox in the expression tab and hiding the label in the axis tab, but this will still leave empty spaces where the bars would be. So I think the only way to solve this would be to use the expressions you used to get to Weekdays and Weekend:

(WeekdayExpression) + (WeekendExpression)

If your expressions are very long, you could always store them in a variable and make it:

($(vWeekdayExpression)) + ($(vWeekendExpression))

Re: Please help with expression

priya945 — Fri, 30 Nov 2018 07:15:09 GMT

Thank you so much.

Could you please guide me if I can achieve the same i.e Weekday + Weekend value in the script

Re: Please help with expression

jensmunnichs — Fri, 30 Nov 2018 07:18:04 GMT

What expressions do you use to calculate Weekday and Weekend?

Also can you please provide a sample of your data and possibly your application, that would really speed things up.

Re: Please help with expression

priya945 — Fri, 30 Nov 2018 07:33:48 GMT

Thanks for your time. I have achieved the requirement in script using below but looking for any optimized(better) than below. This way I have to load the same table several times based on number of filed to concatenate into one(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]

Re: Please help with expression

jensmunnichs — Fri, 30 Nov 2018 07:39:36 GMT

Still would help a lot if you could provide your application and/or a sample of your data as I'm a bit confused about your requirement.

Re: Please help with expression

priya945 — Fri, 30 Nov 2018 08:15:36 GMT

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

Re: Please help with expression

jensmunnichs — Fri, 30 Nov 2018 08:32:13 GMT

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)

Re: Please help with expression

priya945 — Fri, 30 Nov 2018 08:42:12 GMT

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

Re: Please help with expression

jensmunnichs — Fri, 30 Nov 2018 08:56:22 GMT

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.

Re: Please help with expression

priya945 — Fri, 30 Nov 2018 09:37:34 GMT

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

Re: Please help with expression

jensmunnichs — Fri, 30 Nov 2018 10:00:46 GMT

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.

Re: Please help with expression

priya945 — Fri, 30 Nov 2018 10:02:41 GMT

Once again thanks a lot for your time