Reply
Regular Visitor
Posts: 31
Registered: ‎08-22-2018
Accepted Solution

Filtering

Hi 

I have a 3 column with Consumed ,Date,Product Name

Now based on last 6 months i need to divide the product column in to 3 one should show only first 50 Products consumed on the last 6 months next column should show only from 50 to 150, 3rd coloumn should show only150 to 200 or remaining 

How can i make it, i tried using rank function on date but i am not able to divide it in to 3 limited columns like 1 to 50

 

Any help appreciated.


Accepted Solutions
Moderator
Posts: 9,146
Registered: ‎03-06-2016

Re: Filtering

Hi @JAVED,

 

You can create calculated columns below: 

 

0-50 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)<=50,Table1[Price],BLANK())

 

50-150 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)<=150 && DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)>50,Table1[Price],BLANK())

 

>150 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)>150,Table1[Price],BLANK())

 

w4.PNG

 

Best Regards,
Qiuyun Yu 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Attachment

All Replies
Moderator
Posts: 9,146
Registered: ‎03-06-2016

Re: Filtering

Hi @JAVED,

 

Would you please share some sample data and clarify corresponding desired results? 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 31
Registered: ‎08-22-2018

Re: Filtering

Part No.Part NamePriceDate
    
ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68November 07, 2017
ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68November 07, 2017
ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 17, 2017
ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 15, 2017
ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 14, 2017
ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 12, 2017
ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 06, 2017
ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68September 19, 2017

 

Dis is sample data,here i have 10 years of data but i need to display only 6 months data. i need to create 3 columns one should display only 1 to 50 days price of part name 2nd column as 50 to 150 days price of partname remaining as one column.Here i gave ranks to date but i am not able to divide the column in to 3 as desired

Moderator
Posts: 9,146
Registered: ‎03-06-2016

Re: Filtering

Hi @JAVED,

 

You can create calculated columns below: 

 

0-50 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)<=50,Table1[Price],BLANK())

 

50-150 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)<=150 && DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)>50,Table1[Price],BLANK())

 

>150 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)>150,Table1[Price],BLANK())

 

w4.PNG

 

Best Regards,
Qiuyun Yu 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Regular Visitor
Posts: 31
Registered: ‎08-22-2018

Re: Filtering

Thanks for your help that works

Highlighted
Regular Visitor
Posts: 31
Registered: ‎08-22-2018

Re: Filtering

I took it as a pie chart like 0-50,50-100,100-150 and i took the table with product name ,product code , price when i click on 0-50 in pie chart the table is not filtering it shows all values i want to see only 0-50 values.