conditional sum based on two input box

[#1]

Dear all,I have a straight table with 3 dimension (category, company and business unit) and an expression that is the daily balance.

My need is to compare the balance between two different dates.

I've created two variables and two input box date 1 and date 2.

I then used two expression for each input box like this one:

=SUM({$<DATE_REF={"$(=VAR_DATA2)"}>}Balance_CTV2)

=SUM({$<DATE_REF={"$(=VAR_DATA1)"}>}Balance_CTV2)

but the result is always "0" even the data exist.

I've laso tried without the " but always the same 0.

What is wrong in my expression?

Thank you

[#2]

Go to settings -. variable overview - add variable

To check quickly what is the output - goto input box or text object and view the variable values

Text Object : = $(vname)

[#3]

In Qlik is not necessary to define the type of data.

is chosen automatically based on the contents of the field or variable.

Instead in Set Analysis is preferable to call in order to avoid misinterpretation.

Now I am in train for two to three hours.

If you have not solved before , I will look willingly qvw you sent

[#4]

Dear Gennaro,

Unfortunately I have not solved. Please help

Inviato da iPhone

Il giorno 15/apr/2015, alle ore 20:05, Gennaro Voto <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>> ha scritto:

Qlik Community <https://community.qlik.com/?et=watches.email.thread>

conditional sum based on two input box

risposta da Gennaro Voto<https://community.qlik.com/people/g.voto76?et=watches.email.thread> su New to QlikView - Visualizza la discussione completa<https://community.qlik.com/message/754537?et=watches.email.thread#754537>

[#5]

Hi Massimo,

the file you attached is not correct.
It contains only a few lines of the script to load, there is no tab or object.

For this reason I simulated your data, roughly based upon the information provided in different post, and I built with simulated data analysis with correct formulas.

Let me know if they work, otherwise provide me a qvw with data and the table or chart where I can enter formulas and try on your data.

Regards
Gennaro

[#6]

Dear Gennaro,

from your I understand you changed the " with the ' symbol. Unfortunately this not worked for my case. I try to attach again the file. please let me know if now is OK.

Thank you

[#7]

Hi Max,

I saw the new attach and is now complete with tabs and data.
I tried to apply the formulas and to recreate variables and objects, but I'm afraid that there is some problem in the load, so I do not understand how Qlik interpret date values that come from two different sources (QVD and xlsx).

You could try to create a new field during load data QW_SALDI.QVD and cast to date and giving it the data type, so you can use the new field in the Set Analysis to check the data content.

If you want I can fix it myself, but you should send a subset of data (reduced QVD and .xlsx), so to do the tests directly on your data via reload script.

Bye
Gennaro

[#8]

Ciao Gennaro,

many thanks for your help. After some investigation I understand that the problems comes from the oracle DB. Bt unable to understand how resolve. The point is n the filed DATE_REF that under oracle comes with the date format DD-MMM-YYYY. If I try to export the DB on an excel file and use that one all works fine.

I also tried in the load script to use the sintax:

date#(DATE_REF,'DD/MM/YYYY') AS m_date

but the result is that m_date is shown with number and the comparison with the variable doesn't work.

So if I send you a couple of date it will work just because the dield date will be "normalized".

Are you able to tell me any additional suggestion to try?

It my be the next step is to change something in the conditional sum formula

SUM({$<[m_date]={'$(=Date(VAR_DATA2))'}>} Balance_CTV2)

?

Thank you

[#9]

Hi Massimo,

use date#(DATE_REF,'DD/MM/YYYY') AS m_date in script, the road is right, if your dates are represented by numbers is okay.

Eliminates the variables that you have created, replacing them with the new variables and using the calendar object set its value.
Variables will be set automatically with the number corresponding to the date.

The attached file is your file, already modified with the creation of new variables, which are measured with numbers corresponding to the dates.
There are also the correct formulas both for the description of the columns that for the calculation of the sum of BALANCE_CTV2

I hope help you solve the problem permanently

Bye

Gennaro

[#10]

Dear Gennaro,

many thanks for your valuable support. yes we got one step more but not yet solved.

It seems that once done the date#(DATE_REF,'DD/MM/YYYY') AS m_date the only way to see the m_date values is to set it with the sintax date#. For example I've made a text box to see how the value is shown with his sintax.

date#(m_date)& '\n' & 'Data 2: ' & (VarData2)

The sum formule still not working. i tried different ways to have it:

ORIGINAL one

SUM({$<m_date={'$(=Date(VarData2))'}>} Balance_CTV2)

ALSO TRIED WITH:

=SUM({$<date#(m_date)={'$(=(VarData1))'}>} Balance_CTV2)

I think we need to change the sintax of the SUM formula but unsure how.

Thank you

[Marcellino_Groothof]

Hi,

sum(if(DATE_REF = VAR_DATA2, Balance_CTV2) )   check of both data vars are identical

instead

=SUM({$<DATE_REF={"$(=VAR_DATA2)"}>}Balance_CTV2)