Qlik Community

Integration, Extension & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arie_rozzy
Contributor
Contributor

Summary Net by date

hello,

I have a problem with summary by date, I have data like this

Net_omset.JPG

expression column NetAmt: 

if(date=Date_target,Sum(netamt),'')

 

I want to find Netamt total according to column Date Omset

Labels (1)
1 Solution

Accepted Solutions
saurabhwadhwa
Partner
Partner

Since its not in this same table , you can use exists function to check the date and create the flag in similar way .

Step1 :

Table1:

Load Date1 from table1 ; 

Fact table :

Load Date2 , if (exists (Date1 ,Date2), 'Y', 'N') as Date_check  from Fact table ;

Just make sure the date formats are same in the both the fields . otherwise , you can convert in number format and then compare .

Step2 :

use the same expression .

let me know if it does not work . 

Note : you can achieve it in front end without creating the date_check , like you did using the if statement . 

its good to use set analysis in expression and avoid If statement .

BR , 

Saurabh

View solution in original post

3 Replies
saurabhwadhwa
Partner
Partner

Hi , 

If you have both the date fields i.e. date and Date omset in the same table then you can create a flag as below 

If(date = Date omset , 'Y', 'N') as Date_check 

And use this field to filter out the data in the expression .

 Expression:

Sum({<Date_check  = {'Y'}>}netamt).

Hope it helps .

BR,

Saurabh

arie_rozzy
Contributor
Contributor
Author

date_omset and date_target are different tables,

i try expression like this : Sum(distinct netamt)

but the first line appears data_omset out of 3-7-2018 & 2-6-2018

line 2 & 3 is correct

net_omset3.JPG

 

 

saurabhwadhwa
Partner
Partner

Since its not in this same table , you can use exists function to check the date and create the flag in similar way .

Step1 :

Table1:

Load Date1 from table1 ; 

Fact table :

Load Date2 , if (exists (Date1 ,Date2), 'Y', 'N') as Date_check  from Fact table ;

Just make sure the date formats are same in the both the fields . otherwise , you can convert in number format and then compare .

Step2 :

use the same expression .

let me know if it does not work . 

Note : you can achieve it in front end without creating the date_check , like you did using the if statement . 

its good to use set analysis in expression and avoid If statement .

BR , 

Saurabh

View solution in original post