Qlik Sense - Combine 2 rows with same data into one, using an expression

[YG]

I am trying to replicate a report from power BI to Qlik and I am new to Qlik.  The issue is that qlik seems to understand the data from different tables with same data as the same to sum it up. i want to know how to do it.

This is the Qlik Sens OUTPUTThis the Power BI output I want to replicate

The qlik output I used the expression  =Resolve_time/3600

Also I tried,

=NUM(avg(total Resolve_time)/3600,'##.00') (gave me the flat average ) NOT per month average

=NUM(avg(total Resolve_time)/[Opened.autoCalendar.Month]/3600,'##.00') NOT per month average just flat average of SUM

I would like if its an expression that could replicate the 2nd output. Anyone who can help would be appreciated. Thanks 

[Prashant_Naik]

Hi,

You can use dual function in your load script that might help you.

Dual(Date(datefield,'MMM'),month(datefield)) as newmonth

Use this month field in the place of month that you used in table.

Dual function 

Regards,

Prashant

[Rodj · Accepted solution]

I can't be sure I have this right without seeing your source data, but it should be pretty straight forward.

I'm going to assume you are after a table with Fin year and Month as dimensions and then two measures, total resolve time and avg resolve time. If the Resolve time source data is a decimal representation of the time in hours (which it looks to be from the images you supplied) then your measures should be simply sum(Resolve_time) and avg(Resolve_time). You can then set the measure format in the properties to only show the 2 decimal places. The measures will operate over the scope of the dimensions in the table.

This seems too simple though, so I'm probably missing something about your source data structure. What date field are you deriving month from? Is it a single date per month or is it a timestamp? You mention something about Qlik understanding the data from different tables as the same. The Qlik engine works by association rather using set-based join logic as Power BI does. This behaves slightly differently and can seem a little weird until you get used to it and realise that in most scenarios it saves you a heap of time and effort. Perhaps if you describe your source data we can provide a better answer.

Cheers,

Rod

[YG · Accepted solution]

Thanks Rod,  All your assumptions are correct. I have over complicated this issue by using Dimensions instead of measures you have suggested. Its simple answer as you said. Thanks  I got numbers right now